Need to Refactor the SQL Query to reduce the execution time through CPP
The below query execution takes more than two minutes. Using SQL Server.
Select lastname, firstname, gender CASE WHEN TableB.orderno is null THEN TableA.EmpId ELSE TableB.orderno END as 'EmpId'
FROM TableA
LEFT JOIN TableB ON TableA.EmpId = TableB.EmpId
WHERE TableA.userId = ? and TableA.findstring like '%Raj%' and '%Guptha%';
Planning to do indexing and refactoring the query to make the execution faster.
How to refactor the above query?
I tried to split up the query as below, Does the below split-up query as Query#1 & Query#2 is advisable?
How to pass the Query#1 result set(more than 2000 records) to Query#2 as an input in CPP?
Query 1: `Select EmpId FROM TableA WHERE findstring like '%Raj%' and '%Guptha%';`
Query 2:
Select lastname, firstname,gender CASE WHEN TableB.orderno is null THEN TableA.EmpId ELSE TableB.orderno END as 'EmpId'
FROM TableA
LEFT JOIN TableB ON TableA.EmpId = TableB.EmpId
WHERE TableA.userId = ? and TableA.EmpId IN (*RESULT OF Query 1*);
Select lastname, firstname, gender CASE WHEN TableB.orderno is null THEN TableA.EmpId ELSE TableB.orderno END as 'EmpId'
FROM TableA
LEFT JOIN TableB ON TableA.EmpId = TableB.EmpId
WHERE TableA.userId = ? and TableA.findstring like '%Raj%' and '%Guptha%';
Planning to do indexing and refactoring the query to make the execution faster.
How to refactor the above query?
I tried to split up the query as below, Does the below split-up query as Query#1 & Query#2 is advisable?
How to pass the Query#1 result set(more than 2000 records) to Query#2 as an input in CPP?
Query 1: `Select EmpId FROM TableA WHERE findstring like '%Raj%' and '%Guptha%';`
Query 2:
Select lastname, firstname,gender CASE WHEN TableB.orderno is null THEN TableA.EmpId ELSE TableB.orderno END as 'EmpId'
FROM TableA
LEFT JOIN TableB ON TableA.EmpId = TableB.EmpId
WHERE TableA.userId = ? and TableA.EmpId IN (*RESULT OF Query 1*);
Last edited on
what are you trying to do in c++??
Assuming the tables are indexed correctly, the most likely problem is either the like statements or the join statements. I do NOT recommend running multiple statements if you can avoid it. The database knows how to optimize what you asked for, so putting it all together lets it tweak for you. Splitting it forces it to throw any tweaks away and do them one by one. How many do you get if you remove the like statements: maybe you can do that part in c++ faster even if it pulls more data, depends on how much more...
how fast do you need / expect it? Do you have 'explain' available or an admin who has access to explains?
The database fetch libraries should pass you the data from a query in a container already? What are you using here?
Assuming the tables are indexed correctly, the most likely problem is either the like statements or the join statements. I do NOT recommend running multiple statements if you can avoid it. The database knows how to optimize what you asked for, so putting it all together lets it tweak for you. Splitting it forces it to throw any tweaks away and do them one by one. How many do you get if you remove the like statements: maybe you can do that part in c++ faster even if it pulls more data, depends on how much more...
how fast do you need / expect it? Do you have 'explain' available or an admin who has access to explains?
The database fetch libraries should pass you the data from a query in a container already? What are you using here?
Last edited on
I have used Left Join in the existing query,before changes it has taken 0 seconds for execution after the Left join it takes more than 2 minutes.I suspect that findstring like '%Raj%' and '%Guptha%';`takes more time .How to optimize the query
Last edited on
Table has around 10 million records and using ADO connection we are connecting to the database
yes, % at the front or back is better, both at once is slow, in the middle is slow.
any chance the % junk is fixed width on either side?
also, I ask again, how does YOUR findstring work? Can you just do the like part on specific columns instead? It looks troubling but maybe its the dialect of sql.
any chance the % junk is fixed width on either side?
also, I ask again, how does YOUR findstring work? Can you just do the like part on specific columns instead? It looks troubling but maybe its the dialect of sql.
Last edited on
Each row Findstring column holds the value as [Raj,Guptha,56789,Contract,$2000,Software,Bengaluru...] nearly 10 words separated by commas (which are keys to search in UI)
Where % to be added ,you mean to say like this '%Raj and Guptha%'
Can you just do the like part on specific columns instead? - yes ,the like is added on specific column
It looks troubling but maybe its the dialect of sql-Its not clear,could you explain clearly
Where % to be added ,you mean to say like this '%Raj and Guptha%'
Can you just do the like part on specific columns instead? - yes ,the like is added on specific column
It looks troubling but maybe its the dialect of sql-Its not clear,could you explain clearly
Last edited on
there are different sqls -- the core of the 'language' is the same but every database or tool has extensions and slightly different syntax on various statements. Its not always clear what something does if you don't know the specific version of the language.
if the data looks *exactly* like that, try
'Raj,Guptha%'
if the data looks *exactly* like that, try
'Raj,Guptha%'
so either that pattern is not actually correct, or something is wrong with your query. are the commas real or something your c++ injected between the fields? You need to debug the query on the database itself, not calling it with c++, until you have the sql like you need it to be. See what you can do to simplify it that way to get an efficient like statement.
Last edited on
Topic archived. No new replies allowed.