Solved

slow query for me, but fast for others

Posted on 2004-10-05
17
599 Views
Last Modified: 2006-11-17
This is a query that run in seconds for all my colleagues, but takes minutes for me.  What might be the cause of this, and how do I correct it?  (apart from the suggestion that it doesn't like me)   Are there personal settings that affect my query performance?  I am running this on a lightly loaded quad Xeon server with 4GB RAM, and as soon as I run it, the task manager shoots up to 100% and stays there, while for others the results come back in a few seconds.


select
p1.tnum, p1.trddt as 'date',
company,
p1.inserttime as 'p1_in',
p2.inserttime as 'p2_in',
p3.inserttime as 'p3_in'
from trademaster p1, tradesupplement1 p2, tradesupplement2 p3
where  
p1.trddt *=p2.trddt and p1.tnum *= p2.tnum
and p1.trddt *=p3.trddt and p1.tnum *= p3.tnum
and company not like 'mycompany%'
and p2.action like 'sendquote%'
and p2.seq =1
and p3.seq =2
and p1.tstmsg !='yes'  
and p1.trddt = '09/30/04'
0
Comment
Question by:Callandor
  • 7
  • 2
  • 2
  • +4
17 Comments
 
LVL 6

Expert Comment

by:OlegP
ID: 12226072
If result is big it may be problems with net. Try copy a big file from another computer to your  and colleagues computers.
And compare time of copy.
0
 
LVL 69

Author Comment

by:Callandor
ID: 12226104
The result set is 1130 records, which is not that big.  My colleagues are sitting right next to me, and I know they are on the same subnet (we have static IP addresses).
0
 
LVL 6

Expert Comment

by:OlegP
ID: 12226137
You can have bad net card  or bad port of HUB for example (result of it is many bad net packets) e.t.c
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 12

Expert Comment

by:ill
ID: 12226139
are you running it from QA? and if, you both have result to text/to grid ?
0
 
LVL 69

Author Comment

by:Callandor
ID: 12226201
I don't know if it's a network issue, but none of my accesses to network drives or internet is affected.  They seem as fast as before.

I'm running this from Embarcadero Rapid SQL 7.0.0
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12226265
It this query running in T-SQL?  If so, you may want to adjust you syntax as the syntax your using for your Joins is somewhat out of date for T-SQL...

select
p1.tnum, p1.trddt as 'date',
company,
p1.inserttime as 'p1_in',
p2.inserttime as 'p2_in',
p3.inserttime as 'p3_in'
from
    trademaster p1,
    INNER JOIN tradesupplement1 p2 ON
        p1.trddt = p2.trddt and
        p1.tnum = p2.tnum and
        p2.action like 'sendquote%' and
        p2.seq = 1
    INNER JOIN tradesupplement2 p3 ON
        p1.trddt = p3.trddt and
        p1.tnum = p3.tnum and
        p3.seq = 2
where  
    company not like 'mycompany%'
    and p1.tstmsg !='yes'  
    and p1.trddt = '09/30/04'
0
 
LVL 69

Author Comment

by:Callandor
ID: 12226409
No, it's not T-SQL.
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12226521
Mybad, it's PL/SQL?
0
 
LVL 69

Author Comment

by:Callandor
ID: 12226571
Nope, it runs on a vanilla SQL Server 2000.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12232984
Make sure you don't have ODBC traceing  enabled under ODBC Admin under Control Panel+Admin Tools
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12233079
what about PC's - is you same as others (memory, processor, HDD, etc)?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 12233088
>quad Xeon server with 4GB RAM, - is not so bad

When did you run antivirus, spyware removal?
0
 
LVL 69

Author Comment

by:Callandor
ID: 12233613
I'll check the ODBC tracing, to see if it's on, but how does ODBC tie in with using RapidSQL on an SQL Server?

The PCs are roughly the same in capability (I know something about hardware), and my machine was a recent fresh build, after spyware rendered it inoperative.  The only choice was to nuke it and start all over, and I have since not had any problems.  All of us are behind a corporate firewall, with Norton Antivirus running.  

I guess my machine being recently rebuilt is the one distinguishing characteristic from the others, but what role that would play escapes me.
0
 
LVL 69

Author Comment

by:Callandor
ID: 12237595
ODBC tracing is off.
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 12245954
You are all running Embarcadero Rapid SQL 7.0.0 (same version and patches)?  Are you positive the criteria on all the queries are the same?  Have you looked at SQL profiler to make sure the same sql is processed?

Since your machine was recently rebuilt, there is the possibility you have different versions of stuff than the other people (maybe they forgot to install a new MDAC or a patch to RapidSQL).

I also tend to agree with the above that you could have a weird NIC card--we've been bit by this on resultsets many times.  Sometimes they're set to autodetect, but they run at 10mb instead of a faster rate (on the flipside, sometimes they are set to a fast rate and don't negotiate well with the switch/hub).
0
 
LVL 69

Author Comment

by:Callandor
ID: 12250421
Ok, the problem is solved, and boy do I feel dumb.  arbert's comment made me go back to basics and ask my colleagues what version of Rapid SQL they were running: they were running 7.2.0, whereas I was running 7.0.0.  I installed 7.2.1 and ran the same query, and now it zips along just like theirs.  That's quite a performance improvement!  Points will go to arbert for slapping me up the head with a 2x4 and reminding me to check the basics.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12251168
"That's quite a performance improvement!  Points will go to arbert for slapping me up the head with a 2x4 and reminding me to check the basics."

lol...amazing what vendors can fix/improve upon!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sqlquerystress - To test db performance 8 41
hyperlink data type in SQL 3 25
T-SQL Default value in Select? 5 24
T-SQL:  Collapsing 9 22
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question