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. |
|
|
jeff06
Posting Yak Master
166 Posts |
Posted - 2007-01-16 : 14:35:32
|
ThankBut 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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
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 |
|
|
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 problemYou 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?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
jeff06
Posting Yak Master
166 Posts |
Posted - 2007-01-16 : 15:06:53
|
ThanksThe 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 |
|
|
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 modeMy guess is you are filling up the buffer on your machine and it is the bottleneckAgain, 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 helpBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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 LarssonHelsingborg, Sweden |
|
|
jeff06
Posting Yak Master
166 Posts |
Posted - 2007-01-17 : 09:08:26
|
We have exact same machine HP 3.07 Ghz, 512M ram |
|
|
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. |
|
|
|