Solved

slow query for me, but fast for others

Posted on 2004-10-05
17
625 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 43

Expert Comment

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

Expert Comment

by:Eugene Z
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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