0

I am new to SQL and trying to query a large database so speed is an issue. I have been using a query (with line 1) of the form shown below which has been working fine, but when I modify it (to switch line 1 for line 2) to use a constant to make a cut rather than a value derived within the query itself then the query is significantly slower (running time of 1 is ~1sec and 2 is a few minutes). I would have actually expected it to be much quicker. Can someone explain why this is happening or suggest how I might rewrite this query better?

Thanks

Query

with local_sample as 

( SELECT b.mass, ...various other columns selected... 

FROM table1 TAB, table2 b 

WHERE ...a few clauses... )


SELECT min(prog.num), LTAB.mass, ...various other columns...

from local_sample LTAB, table2 prog

WHERE ...a few clauses... 

[**1**] and prog.mass > LTAB.mass/2.0

[**2**] and prog.mass > 31.62

group by ...columns...
D Stanley
  • 149,601
  • 11
  • 178
  • 240
LizE
  • 1
  • 3
    Tag dbms product used. (Very product specific issue...) – jarlh Oct 20 '15 at 14:39
  • 1
    Is there an index on `prog.mass`? How many rows (out of the total) meet that criteria? – D Stanley Oct 20 '15 at 14:39
  • I'm not sure what you mean by is there an index? How should I find that out? Re your question about rows, it is possible there are significantly more rows that meet the second criteria than the first, which I suppose could explain the difference in speed, but I'd be surprised if it were the only culprit. I suppose I was asking to see if anyone who knows about SQL saw an obvious problem with the second query. – LizE Oct 20 '15 at 14:44
  • Nothing obvious - it's more likely that the compiler chooses a different query plan that is (apparently) much less efficient, or the number of rows increases so much that it does a table scan vs an index lookup, etc. – D Stanley Oct 20 '15 at 15:05
  • So which DBMS are you using? Postgres? Oracle? –  Oct 20 '15 at 16:39

1 Answers1

0

Information in the question is kind of scarce, so at a guess, it's an implicit conversion issue. My hunch is LTAB.mass is the same data type as prog.mass, so no conversion is necessary, but whatever that data type is doesn't play nicely with decimal.

Numbers in sql come in many flavors, and most of the time we don't have to think about it because the conversions are very fast and happen in the background. Occasionally though, you'll come across number types formats that don't play well with others (float for instance) and it can become a performance pain point.

So here is a way to test if that's the issue run the below query (assuming Microsoft SQL Server is your RDBMS):

select SQL_VARIANT_PROPERTY(Mass,'BaseType') AS 'Base Type'
From table2

If my hunch is correct it will return float as the base type. If that's the case and the implicit conversion is the issue then the below should work in a similar manner to query 1:

with local_sample as 
( SELECT b.mass, ...various other columns selected... 
FROM table1 TAB, table2 b 
WHERE ...a few clauses... )

Declare @Mass float = 31.62

SELECT min(prog.num), LTAB.mass, ...various other columns...
from local_sample LTAB, table2 prog
WHERE ...a few clauses... 
and prog.mass > @Mass
group by ...columns...

Anyway let me know if that doesn't work for you.

Randall
  • 1,441
  • 13
  • 19