homepage Welcome to WebmasterWorld Guest from 54.205.106.111
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
slow data
stevelibby




msg:3507481
 12:07 pm on Nov 17, 2007 (gmt 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!

 

duckhunter




msg:3508852
 3:25 pm on Nov 19, 2007 (gmt 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?

stevelibby




msg:3509917
 4:53 pm on Nov 20, 2007 (gmt 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.

defanjos




msg:3510033
 7:02 pm on Nov 20, 2007 (gmt 0)

are you using indexes in your db?
if not, you probably should.

stevelibby




msg:3510406
 10:38 am on Nov 21, 2007 (gmt 0)

im not massively great with db, how will indexes help spped the retrival up?

stevelibby




msg:3510407
 10:38 am on Nov 21, 2007 (gmt 0)

im not massively great with db, how will indexes help speed the retrival up?

defanjos




msg:3510644
 4:57 pm on Nov 21, 2007 (gmt 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.

LifeinAsia




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

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.

duckhunter




msg:3511678
 12:32 am on Nov 23, 2007 (gmt 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.

stevelibby




msg:3512905
 11:15 am on Nov 25, 2007 (gmt 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.

stevelibby




msg:3513487
 11:18 am on Nov 26, 2007 (gmt 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?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved