locked
SQL Query Taking Too Long RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for your help in advance.  I have do develop a query that joins two table within one database with a third table from a separate database.  Here is my query:

    Select * from (
    SELECT        *  
    FROM            tblPtmstr1 INNER JOIN
                             tblPTDEMO2 ON tblPtmstr1.PatientID = tblPTDEMO2.MRNumber 
     where tblPtmstr1.PatientID in
    (Select PatientID from signalr.dbo.tblappointments Where ApptDateTime Between '01/03/2016' and '01/05/2016' and CancellationReason is null))
    
    as Appointments Where Appointments.Ins1Code = '1234'

    My problem is that this query takes approximately 12 seconds to execute.  The Select from signalr.dbo.tblappointments returns approximately 100 records.  tblPtmsrt1 and tblPtDemo2 have approximately 25,000 records total.  I am not sure how to restructure this query to speed things up because this seems inordinately slow.  I also tried it without the alias:

    SELECT        *  
    FROM            tblPtmstr1 INNER JOIN
                             tblPTDEMO2 ON tblPtmstr1.PatientID = tblPTDEMO2.MRNumber 
     where tblPtmstr1.PatientID in
    (Select PatientID from signalr.dbo.tblappointments Where ApptDateTime Between '01/03/2016' and '01/05/2016' and CancellationReason is null)) and tblPTDemo2.Ins1Code = '1234'
    
    

    but that didn't seem to help.  Any insight would be greatly appreciated.

    Tuesday, January 12, 2016 7:44 PM

All replies

  • User1122355199 posted

    AS a follow up to my previous post, when I try the query:

    Select * from (
    SELECT        *  
    FROM            tblPtmstr1 INNER JOIN
                             tblPTDEMO2 ON tblPtmstr1.PatientID = tblPTDEMO2.MRNumber 
     where tblPtmstr1.PatientID in
    (Select PatientID from signalr.dbo.tblappointments Where ApptDateTime Between '01/03/2016' and '01/05/2016' and CancellationReason is null))
    
    as Appointments

    Removing the Where clause to the alias table, the query executes almost instantly.  In this particular example, it returns only 35 rows.  So the filter:

     where Appointments.Ins1Code = '1234'

    is causing the bottleneck.  By adding this filter, only 18 records are returned.

    Tuesday, January 12, 2016 7:55 PM
  • User614698185 posted

    Hi kmcnet,

    Firstly, if you don't need to all the fields, you could select column directly, like below:

    SELECT column_name,column_name FROM table_name;

    Secondly, you could use Stored Procedures. Stored procedures can improve performance, please see:

    https://technet.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

    Best Regards,

    Candice Zhou

    Wednesday, January 13, 2016 8:16 AM
  • User1633621018 posted

    Hi Kmcnet,

    You can do one thing which will improve the query performance. You can put the where condition query in temp table and use it in where condition.

    CREATE TABLE tmp#(PatientID int)
    INSERT INTO tmp#
    	(PatientID) 
    SELECT 
    	PatientID 
    FROM 
    	signalr.dbo.tblappointments 
    WHERE 
    		ApptDateTime Between '01/03/2016' and '01/05/2016' 
    	AND CancellationReason is null 
    	
    SELECT *
    FROM
    	tblPtmstr1 
    	INNER JOIN tblPTDEMO2 ON tblPtmstr1.PatientID = tblPTDEMO2.MRNumber
    WHERE tblPtmstr1.PatientID in(SELECT PatientID FROM tmp#)

    Also I would suggest you to use stored procedure, it will also help in improving the performance.

    Wednesday, January 13, 2016 9:05 AM
  • User1122355199 posted

    Thanks so much for the response.  Reducing the number of columns had no effect on execution time, nor did the temp table solution once the WHERE clause was implemented.  Any other ideas?

    Friday, January 15, 2016 2:15 PM
  • User614698185 posted

    Hi kmcnet,

    As my previous post suggested, you could use Stored procedure. Stored procedures can improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server.

    For more information, please see: https://en.wikipedia.org/wiki/Stored_procedure

    Best Regards,

    Candice Zhou

    Monday, January 18, 2016 8:47 AM