Welcome to WebmasterWorld Guest from 54.225.33.25

Forum Moderators: ocean10000

Message Too Old, No Replies

slow data

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

Preferred Member from GB 

10+ Year Member

joined:Jan 26, 2004
posts:636
votes: 0


hi
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)

Preferred Member

10+ Year Member

joined:Oct 28, 2002
posts:506
votes: 0


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)

Preferred Member from GB 

10+ Year Member

joined:Jan 26, 2004
posts:636
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 12, 2002
posts:857
votes: 0


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

Preferred Member from GB 

10+ Year Member

joined:Jan 26, 2004
posts:636
votes: 0


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

Preferred Member from GB 

10+ Year Member

joined:Jan 26, 2004
posts:636
votes: 0


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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 12, 2002
posts:857
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5620
votes: 44


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)

Preferred Member

10+ Year Member

joined:Oct 28, 2002
posts:506
votes: 0


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)

Preferred Member from GB 

10+ Year Member

joined:Jan 26, 2004
posts:636
votes: 0


Hi
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)

Preferred Member from GB 

10+ Year Member

joined:Jan 26, 2004
posts:636
votes: 0


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?