Solved

slow query for me, but fast for others

Posted on 2004-10-05
17
580 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 12

Expert Comment

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

Author Comment

by:Callandor
Comment Utility
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
Comment Utility
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
Comment Utility
No, it's not T-SQL.
0
 
LVL 6

Expert Comment

by:RaisinJ
Comment Utility
Mybad, it's PL/SQL?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 69

Author Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

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

Author Comment

by:Callandor
Comment Utility
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
Comment Utility
ODBC tracing is off.
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
"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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now