Welcome to WebmasterWorld Guest from

Forum Moderators: ocean10000

Message Too Old, No Replies

slow data

12:07 pm on Nov 17, 2007 (gmt 0)

10+ Year Member

i am having trouble with a search query that takes to long and i cant understand why.
i am using a straight forward query governed by parameters and then using a getrows procedure i show the results, for some reason its not quick, the table itself is around 30000 records..
Can you help me understand why i lags!
3:25 pm on Nov 19, 2007 (gmt 0)

10+ Year Member

Using GetRows, I'm assuming you are using Access? 30K rows in Access is kinda pushing the limits. Also, if there are multiple users hitting it at the same time, forget about it (scale up to MySQL or SQL Server).

Make sure your closing connections after you use them. Access is bad about memory leaks with orphaned connections.

What is the SQL Statement? Are your 'where' clauses on fields that are indexed? Are you using 'LIKE' in your SQL statement?

4:53 pm on Nov 20, 2007 (gmt 0)

10+ Year Member

hi i use mssql, the query is 'where' and '=' not like. Would it be better if i filtered after getting the whole record set?
also it takes a second using enterprise manager.
7:02 pm on Nov 20, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

are you using indexes in your db?
if not, you probably should.
10:38 am on Nov 21, 2007 (gmt 0)

10+ Year Member

im not massively great with db, how will indexes help spped the retrival up?
10:38 am on Nov 21, 2007 (gmt 0)

10+ Year Member

im not massively great with db, how will indexes help speed the retrival up?
4:57 pm on Nov 21, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

I am no expert either, but I know you are supposed to have them, and they increase the speed of data retrieval, especially.

Do some searches for "mssql indexes" and "database indexes" - you'll get more info on them.

5:09 pm on Nov 21, 2007 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

Yes, indexes should help. But if the query finishes in a second in Enterprise Manager, but takes a long time to run/be displayed through the web page, I suspect the underlying problem is something other than indexes. Most likely it's something else on the page that is causing the problem. But we need some more details about what is happening.

First, post your query so we see exactly what you're doing, then we can also suggest proper indexing.

Second, post the relevant parts of your code.

Third, let us know your basic configuration- version of MSSQL, version of Windows, type of application (ASP, PHP, etc.), dedicated or shared server, etc.

12:32 am on Nov 23, 2007 (gmt 0)

10+ Year Member

Also, what type of connection are you using? ie: in your connection string there is something like 'provider='. Reason I ask is that ODBC can be sluggish in certain situations.
11:15 am on Nov 25, 2007 (gmt 0)

10+ Year Member

I use Provider=sqloledb as mt connection string.
then for collection of data i use getrows() then a for next loop to present relevant data.

I have been reading up on indexes and if i have got it right it kind of groups records? so if you have 30000 records of the alphabet you can select that as an index and that will then group by letter, have i got that right?
If i have then lets move on...i already have a primary key, so how do i now create this index, i use enterprise manager.

11:18 am on Nov 26, 2007 (gmt 0)

10+ Year Member

it appears that creating an index in table seems to have speeded things up slightly, now if i have 2 related tables is it possibly to index these? or create query?

Featured Threads

Hot Threads This Week

Hot Threads This Month