SQL Between + OR slow

Question

I have a query which is following.

    select   
a,
b,
c,
CAST(d as bigint) as d,
e,
document_cat,
g,
h,
i,
fiscal_year,
k,
l,
m,
n,
account,
p,
q,
r,
s,
t,
u,
v,
CONVERT(date, CONCAT(i,'01')) as [CalDate],
CASE when r='' then CONCAT(d,'-', [account],'-','X','-',[fiscal_year],'-',p)
          else CONCAT(d,'-',[account],'-',[subsidiary],'-',[fiscal_year],'-',p) 
             end as [UQID],
CASE when r='' then [account] else CONCAT([account],'-',[r]) end as [GL]

FROM
Ledger
WHERE [company]=123456
AND (document_cat= 'CAT A' or document_cat= 'CAT B' or document_cat= 'CAT    C'.....'CAT I'
)
AND (fiscal_year]=2018 or fiscal_year =2019)
AND account BETWEEN 400000 and 999999

It is an extremely slow query.

Can you please advise how to make this fast.

I am not the DBA and I only have access to the views.

The column types are following

+--------------+-----------+
|     Name     | Data_Type |
+--------------+-----------+
| a            | varchar   |
| b            | datetime  |
| c            | numeric   |
| d            | varchar   |
| e            | int       |
| document_cat | varchar   |
| g            | varchar   |
| i            | numeric   |
| fiscal_year  | numeric   |
| k            | date      |
| l            | date      |
| m            | varchar   |
| n            | varchar   |
| account      | varchar   |
| p            | tinyint   |
| q            | varchar   |
| r            | varchar   |
| s            | varchar   |
| t            | varchar   |
| u            | varchar   |
| v            | varchar   |
| h            | varchar   |
| company      | varchar   |
+--------------+-----------+

This is an edit to the original question. I have edited the code to include more detailed code and more details about the data type pf the columns used in this code.

`document cat = 'CAT A'` is invalid SQL. The space will cause a problem. And if `ledger` is a view, then you need to know what the underlying query is, so you know what the database is doing. — Gordon Linoff, Jan 07 '20 at 16:48
"somewhat following" and no info on the schema/keys make this next to unanswerable. — underscore_d, Jan 07 '20 at 16:50
Is `document_cat` indexed? Are the other columns in your `WHERE` condition indexed? — Martin, Jan 07 '20 at 16:53
Can you share the column types of table `ledger`. There is the potential that there is an implicit casting to a varchar here and that can be an expensive operation if the table is large. — JNevill, Jan 07 '20 at 16:55

score 1 · Answer 1 · answered Jan 07 '20 at 18:22

You could convert your OR statements to IN statements. It may help improve performance in MySQL. In other RDBMS, it may not, but it would make your code more readable.

Select a,b,c.....x,y,z from ledger 
where 
 company = 123456
 AND document_cat IN ('CAT A','CAT B','CAT C',.....,'CAT I')
 AND fiscal_year IN (2018,2019)
 AND account between 400000 and 999999

score 0 · Answer 2 · answered Jan 07 '20 at 16:54

The query is fairly simple. If you don't have an access to other objects like indexes created on table ledger we can't really advise you on how to optimize this query. Also, what does "extremely slow" means? Does it ever return any data? I suggest you talk to your DBA to find out what indexes are created on the table and review the explain plan to see what's really going on under the hood. If the table is large (like 100M+ rows) and there is no index for example on the company column, there's a need to scan the entire table to return the data which is hugely affecting the performance of this SELECT query as your memory and disk is getting ton of hits.

With your current privileges your hands are tied. You should probably escalate to someone who can dive deeper than you.

Also, just a thought - ledger may as well be a view, not a table which would actually only complicate things and will need more attention and data/object investigation

thanks for the advice. I am not sure on which columns the indexes are created. Currently the query takes about 30 minutes to return the result. This view has 20M+ rows currently. Also, do I understand that by having indexes created (by DBA) on the columns where I am using AND, OR, BETWEEN syntaxes would make this query faster? — smpa01, Jan 07 '20 at 17:00

SQL Between + OR slow

2 Answers2