sqlsql-serversql-server-2008ssmsssms-2014

Performing of my basic query taking long time

I use MsSQL. I have a "jobs" table which has 140 columns and includes more than 4 million records in it. This table's columns mostly varchar and bit.

The table's 40 columns connected to some other tables. Like "issuerid" from "issuers" table, "fileid" from "files"...

The indexes of table is only on the "fileid" which is non-unique and non-clustered.

My basic query is like in the following:

 select issuerid,count(id) as total , sum(case when X_Status=1 then 1 else 0 end) P_Count  
from jobs  where 1=1  and issuerid='1001' and  creationdate between '01/01/2019 12:00:01 AM' and '06/30/2019 11:59:59 PM' group by issuerid

The duration of the query is: 1min 20seconds (The PC has SSD and 4GB Ram)

So i tried to index on issuerid but it didn't affect as much.

I have a lot of queries on this table for my asp page. For example the sum case changes mostly; sum(case when Y_Status=1 then 1 else 0 end) P_Count Like this.

So even tried to let 2 columns in the table and executed this query select count(id) as, sum(case when X_Status=1 then 1 else 0 end) P_Count from newjobs where 1=1 and this took around 30seconds.

I read many topics and article to improve query performance but didn't work. Is there anyone who has any idea to share?

Thank you.

Solution

  • The following should work for your exact query:

    CREATE NONCLUSTERED INDEX IX_Jobs__IssuerID_CreationDate ON dbo.Jobs (IssuerID, CreationDate)
    INCLUDE (X_Status);

    Since your query filters on IssuerID and CreationDate these are the key columns, then I hav eadded X_Status as a non key column so that the whole query can be run from this index and there is no chance of a bookmark lookup or an index scan.

    As an aside, your current where clause will always exclude things that happen in the first second of the first day and the last second of the last day (i.e between 00:00:00 and 00:00:01on 1st January, and 06/30/2019 23:59:59 and 07/01/2019 00:00:00). This may be deliberate, but I suspect it isn't. It is usually much better, and also more clear as to your intentions to use an open ended date range.

    WHERE   CreationDate > '20190101'
AND     CreationDate < '20190701'

    Or More likely:

    WHERE   CreationDate >= '20190101'
AND     CreationDate < '20190701'

    I have also swtiched to a culture invariant date time format, so that the date literal is interpretted as the same date on every machine. For more reading see: