SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 extreme slow sql query analyzer
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jeff06
Posting Yak Master

166 Posts

Posted - 01/12/2007 :  16:48:56  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 01/13/2007 :  10:09:46  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 01/13/2007 10:10:49
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 01/16/2007 :  14:35:32  Show Profile  Reply with Quote
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 - 01/16/2007 :  14:39:21  Show Profile  Reply with Quote
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 - 01/16/2007 :  14:48:59  Show Profile  Reply with Quote
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 - 01/16/2007 :  14:53:55  Show Profile  Reply with Quote
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 - 01/16/2007 :  15:06:53  Show Profile  Reply with Quote
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 - 01/16/2007 :  15:16:17  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 01/16/2007 :  16:40:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 01/17/2007 :  09:08:26  Show Profile  Reply with Quote
We have exact same machine HP 3.07 Ghz, 512M ram
Go to Top of Page

jeff06
Posting Yak Master

166 Posts

Posted - 01/18/2007 :  12:55:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000