• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

slow query for me, but fast for others

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
Callandor
Asked:
Callandor
  • 7
  • 2
  • 2
  • +4
1 Solution
 
OlegPCommented:
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
 
CallandorAuthor Commented:
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
 
OlegPCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
illCommented:
are you running it from QA? and if, you both have result to text/to grid ?
0
 
CallandorAuthor Commented:
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
 
RaisinJCommented:
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
 
CallandorAuthor Commented:
No, it's not T-SQL.
0
 
RaisinJCommented:
Mybad, it's PL/SQL?
0
 
CallandorAuthor Commented:
Nope, it runs on a vanilla SQL Server 2000.
0
 
Ken SelviaRetiredCommented:
Make sure you don't have ODBC traceing  enabled under ODBC Admin under Control Panel+Admin Tools
0
 
Eugene ZCommented:
what about PC's - is you same as others (memory, processor, HDD, etc)?
0
 
Eugene ZCommented:
>quad Xeon server with 4GB RAM, - is not so bad

When did you run antivirus, spyware removal?
0
 
CallandorAuthor Commented:
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
 
CallandorAuthor Commented:
ODBC tracing is off.
0
 
arbertCommented:
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
 
CallandorAuthor Commented:
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
 
arbertCommented:
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now