February 1, 2006 at 8:30 am
I have a query that returns 71,000 rows from a table with 340,000 rows. On my development machine (Dell Optiplex Gx-280) it takes 20 seconds to return the data. On a secondary machine (IBM t-43 Laptop) it takes 4-5 seconds to return the same data.
If I do count(*) instead of returning the rows, both machines take 1 second to return the count.
Both machines are running Windows XP Pro Sp2 . The development machine has a 2.8 Mhz processor with 1 GB of memory. The other machine has a 1.8 GB processor with 1.5 GB of memory. Both machines have the same SQL ODBC driver (MDAC 2.8). As far as I can tell the Connection Properties are the same on both machines.
I have asked others in our group to test the query, and their times vary from 4 to 7 seconds to return the same data.
For the sake of taking out the network component, I copied the table from the network SQL Server to my local machine and re-ran the query. I had the same results.
My question is does anyone have any thoughts as to where the bottleneck could be?
John
February 1, 2006 at 9:00 am
FYI - Problem seems to have been that Visual Studio Analyze was running. I turned it off under Tracing in the ODBC Administrator, and the query result time was reduced to 5 seconds.
John
February 1, 2006 at 9:01 am
Assuming you're running both queries in Query Analyser ... instead of executing the queries, hit CTRL-L to get the execution plan.
Are the plans the same ?
If plans are not the same then
- Do both tables have the same indexes ?
- Do both tables have a clustered index ?
- Have you updated statistics on both tables ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply