Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 extreme slow sql query analyzer

Author  Topic 

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-12 : 16:48:56
My sql query analyzer my windows xp machine is very slow. I run a query in my collegues computer, it takes about 2 mins to finish. This might means the sql server has no problems. On my computer, the same query took about 3 hours to finish.
Can someone give me some suggestion how to fix this problem.

THX
Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-13 : 10:09:46
Are these connecting to the same server?
Have a look at the query plan. If these are the same maybe it has problems delivering the resultset - make the query a derived table and do a count(*) from it to check.
Also display the results as text rather than a grid do it doesn't have to use as much disk.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-16 : 14:35:32
Thank
But the main problem the sql analyzer is very slow.
For example:
I have a qurey
select *
from a;

a has 40,000 records and 100 columns.
It will take about 1 hr to finish.
I am not sure this is because of my machine or the sql server.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 14:39:21
Well it has to put the results somewhere...what do you have running, and what's the config of themachine

Also how long does SELECT COUNT(*) take to run?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-16 : 14:48:59
SELECT COUNT(*)
from a
takes about 1 second and return 54644.
How to check the configuration of the machine?
what do mean by "what do you have running"?
Thanks
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 14:53:55
quote:
Originally posted by jeff06

SELECT COUNT(*)
from a
takes about 1 second and return 54644.
How to check the configuration of the machine?
what do mean by "what do you have running"?
Thanks



Well, you just elimiated the server as the problem

You do know that you are passing quite a volume of data...did you try changing it as nr as has suggested.

Now let me ask you....what are you going to do with all that data in QA?

I doubt you are manually going to interogate the data.

So what is it you need to do in the long run?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-16 : 15:06:53
Thanks
The main point is that I know the sql sever has no problem. because i run same query in my fried's computer use her account (connect to same sql server), it only take 8 seconds to finish (but in my machine the same qurey take more than one hour to finish)
The problem i guess is either my account in sql server has problem (I post a help request to the DBA but get no response. They are not as friedly as people in the forum) or the configuration for sql query analyzer has a problem. I just not sure what is a starting point for me to investigate if the sql query analyzer has a problem.

Thank you very much. Any sugessions or advice will be greatly appreciated.

JP
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-16 : 15:16:17
Did you change the output rersults to bi in text mode and not in grid mode

My guess is you are filling up the buffer on your machine and it is the bottleneck

Again, what are you trying to do? Why are you trying to return so much data to your machine?

Did she have hers in text mode.

Do you need the data to create a spreadsheet for example.

We need to know what your end result is to be....or is this jus an exercise as in why your machine takes so long?

That's a lot of data to retrun to QA.

Also, some times rebooting your machine will help



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 16:40:15
Also, so you have a 10 mbit NIC and your friend a 100 mbit NIC?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-17 : 09:08:26
We have exact same machine HP 3.07 Ghz, 512M ram
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-18 : 12:55:21
I found the reason for the slowness.
The ODBC "tracing" was turned on on my machine. After I turned it off, the qurey becomes very fast now.

Thanks.
Go to Top of Page
   

- Advertisement -