Need to Optimize query

1

Hi this is my first Query ..i need help to optimize queries.My application's performance is too slow. End users are 100 to 150 in future it may vary to 300. Performing 3 inner joins on 3 tables. For every execution it takes almost 7-8 seconds. And data volume is 17L- 20L .

2

should i share query for better understanding..

3

Please post table schemas and any index you may have on each table?

4

i am unable to post schema and query

5

Could you explain the reason why?
Confidential? Technical?

6

Assuming 17L-20L is lahks - then the first question is why are you returning that many rows to the client? How is the client going to consume that much data and process it?

Second question - how long is the actual query taking vs how long the data takes to cross the network?

And finally - without being able to see the table definitions with indexes and/or execution plans for this query there really is not any way we can help. You could post the query and if there is anything obvious - we could point that out.

7

You haven't given us much to go on.

  1. Are all tables indexed on the columns that you are joining on?
  2. Do those indexes include columns you are returning to the client in the INCLUDE part of the index
  3. If you are returning large volumes of data to each client, have you considered that the Network may be the problem. Its takes time to push large volumes of data over a network
8

its not confidential i am not able to post more information ..its shows alert message ..new members can only post 2 links.

9

We do have software which fetch data (contact number with other details) from 17L-20L rows. its predective dialer.

10

But you can post ddl and dml

Create table sample(I'd int, name varchar(50)

Insert into sample
Selct 1, "han' union
Select 2, 'luke'

11

CallList
ID int NOT NULL,
CampaignID int NOT NULL,
Name varchar(50) NOT NULL,
Description varchar(1024) NULL,
Remarks varchar(1024) NULL,
DOC datetime NULL,
DOM datetime NULL,
Active bit NULL,
BucketSize int NULL,
Priority int NULL,
Prefix nvarchar(50) NULL,
IsPrefixActive bit NULL,
CONSTRAINT PK_CampaignCallList PRIMARY KEY CLUSTERED --
index key column (campaignid)

12

CallListDetails
ID bigint NOT NULL,
CallListID int NULL,
FirstName nvarchar(500) NULL,
MiddleName nvarchar(500) NULL,
LastName nvarchar(500) NULL,
Telephone nvarchar(50) NULL,
Details text NULL,
ScheduledTime datetime NULL,
Remarks text NULL,
Attempts int NULL,
DOC datetime NULL,
DOM datetime NULL,
Status int NULL,
FlagID int NULL,
UserID int NULL,
AssignedBy int NULL,
AssignedDate datetime NULL,
CallBackUserId int NULL,
CONSTRAINT PK_CallListDetails_1 PRIMARY KEY CLUSTERED

index key column (Calllistid,status,userid,assigndate)

13

query - Select * From CallListDetails Where Status = 3 AND CallListID In(Select ID from CallList
where campaignID = @campaignid) AND dbo.DateOnly(AssignedDate) !< dbo.DateOnly
(GETDATE()) -10 and dbo.DateOnly(ScheduledTime)<=dbo.DateOnly(GETDATE()+3) order by ScheduledTime Asc

14

Campaign
ID int IDENTITY(1,1) NOT NULL,
Name varchar (1024) NOT NULL,
Description text NULL,
StartDate datetime NULL,
EndDate datetime NULL,
Active bit NOT NULL,
Port int NOT NULL,
DOC datetime NULL,
DOM datetime NULL,
CallListType smallint NOT NULL,
MaxAttempts int NULL,
HoldPromptID int NULL,
DispositionAlgorithm int NOT NULL,
GreetingID int NULL,
CampaignMode int NULL,
CallFlow varchar(1024) NULL,
BranchCode nchar(50) NULL,
CONSTRAINT PK_Campaign PRIMARY KEY CLUSTERED

15

  1. do you have any indices on these tables especially on Foreign key columns and filtering columns used in where clause?

    Status
    CallListID
    campaignID
    AssignedDate
    ScheduledTime

  2. Also is this for Microsoft SQL Server? or some other database type?

  3. This looks like inline SQL Query. recommend you use stored procedure.

great and now please provide usable sample data in the form of

insert into Campaign
select ' Virendra for President for Life', ' Bharatiya Janata Party',
 '2020-01-01', '2080-12-30', 1, 

etc
16

Just focusing on the query provided:

Select *
   From CallListDetails
  Where status = 3
    And CallListID In ( Select ID
                          From CallList
                         Where campaignID = @campaignid)
    And dbo.DateOnly(AssignedDate) !< dbo.DateOnly(getdate()) -10
    And dbo.DateOnly(ScheduledTime) <= dbo.DateOnly(getdate() + 3)
  Order By
        ScheduledTime asc

I do not see 3 joins...this query is on a single table but it will not perform well due to the functions in the where clause. You are also comparing the ScheduledTime to a date...not sure how that would even work.

For the AssignedDate filter - what is the requirement? Are you looking for all assigned dates that are at least 10 days in the future - or is it something else?

17

yes you understood right on assign date.. ScheduledTime is like customer ask to callback on particular date time so we do punch callback date in ScheduledTime and need to check every time is there any callback Scheduled

18

So - to confirm, the ScheduledTime is actually a datetime column and you want to return the row if:

AssignedDate is more than 10 days in the past
And
ScheduledTime is less than 3 days in the future

If today is 2019-03-15 then include all AssignedDate >= '2019-03-15 00:00:00.000' and ScheduledTime < '2019-03-19 00:00:00.000'

Something like this?

AssignedDate >= dateadd(day, datediff(day, 0, getdate()) - 10, 0)
And ScheduledTime < dateadd(day, datediff(day, 0, getdate()) + 4, 0)

This will allow for any indexes on the AssignedDate or ScheduledTime to be utilized. This will include all ScheduledTime entries up to but not including the 4th day from today.

If you don't have an index on either of these columns - then your query will most likely use a clustered index scan as that would be less expensive than a partial lookup to the index you have shown.