May 24, 2016 at 1:36 am
I have a stored procedure that is running fine but I would like to improve the query speed.
a.report_item = 'Test' is slowing the query speed and I have tried to change it to INT datatype but didn't notice any performance difference.
The tables used are listed below:
ReportItem
id [primary key clustered index]
report_item (nvarchar (100))
flow_name (nvarchar(50))
source (nvarchar(50))
Tech_Details
id [primary key clustered index]
flow_name (nvarchar(50))
source (nvarchar(50))
lang (nvarchar(5))
start_time (datetime)
Query:
SELECT (dateadd(dd,0, datediff(dd,0,b.start_time))) as date, (a.source + '-' + a.report_item) as report_item
, SUM(CASE WHEN (b.lang = 'EN' AND b.sel_test = 1 AND a.report_item = 'Test')
OR (b.lang = 'EN' AND b.sel_test2 = 1 AND a.report_item = 'Test2')
THEN 1 ELSE 0 END) AS EN
, SUM(CASE WHEN (b.lang = 'BM' AND b.sel_test = 1 AND a.report_item = 'Test')
OR (b.lang = 'BM' AND b.sel_test2 = 1 AND a.report_item = 'Test2')
THEN 1 ELSE 0 END) AS BM
FROM ReportItem a INNER JOIN TechDetails b ON a.source = b.source
WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate
AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
AND a.report_id =8 AND a.flow_name = @flow_name
GROUP BY dateadd (dd, 0, datediff(dd, 0, b.start_time)), a.flow_name, a.report_item, a.source
ORDER BY dateadd (dd, 0, datediff(dd, 0, b.start_time))
Output:
Date (start_time)--report_item----EN (lang)---BM (lang)
2015-01-01-------test----------- 3--------- 0
2015-01-01-------test----------- 1----------2
2015-01-02-------test2-----------6----------2
2015-01-02-------test2-----------0--------- 7
Please guide me on where should I look into, thanks !
May 24, 2016 at 2:05 am
Can you please confirm which version of SQL Server you are using? You've posted in the SQL Server 7,2000 forum section.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 24, 2016 at 2:23 am
Sorry did not aware of that.
I am using SQL Server 2008.
Can moderator help to move into appropriate section?
May 24, 2016 at 2:33 am
It's good news. Try this:
SELECT
CAST(b.start_time AS DATE) as date,
(a.source + '-' + a.report_item) as report_item,
SUM(CASE WHEN (b.lang = 'EN' AND b.sel_test = 1 AND a.report_item = 'Test')
OR (b.lang = 'EN' AND b.sel_test2 = 1 AND a.report_item = 'Test2')
THEN 1 ELSE 0 END) AS EN,
SUM(CASE WHEN (b.lang = 'BM' AND b.sel_test = 1 AND a.report_item = 'Test')
OR (b.lang = 'BM' AND b.sel_test2 = 1 AND a.report_item = 'Test2')
THEN 1 ELSE 0 END) AS BM
FROM ReportItem a
INNER JOIN TechDetails b
ON a.source = b.source
WHERE CAST(b.start_time AS DATE) >= @StartDate
AND CAST(b.start_time AS DATE) <= @EndDate
AND a.report_id = 8
AND a.flow_name = @flow_name
GROUP BY
CAST(b.start_time AS DATE),
a.flow_name,
a.report_item,
a.source
ORDER BY CAST(b.start_time AS DATE)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 24, 2016 at 2:46 am
The new query is slower.
May 24, 2016 at 3:06 am
Table definitions, index definitions and execution plan please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2016 at 3:10 am
jc85 (5/24/2016)
The new query is slower.
AS Gail said, and the execution plan of the new query please. "Actual" plans as .sqlplan file attachments.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 24, 2016 at 3:15 am
Hi jc85,
first I would try to use
CONVERT(char(10),b.start_time,121)
instead of dateadd(dd, 0, datediff(dd, 0, b.start_time)).
Furthermore I advise you not to use functions in WHERE caluses. Modify your parameters so that their values represent the correct date. If you do this, the WHERE clause needs no function and the database engine calculates a better execution plan.
Good Luck 🙂
Best regards
Henning
May 24, 2016 at 3:19 am
Henning Rathjen (5/24/2016)
Hi jc85,first I would try to use
CONVERT(char(10),b.start_time,121)
instead of dateadd(dd, 0, datediff(dd, 0, b.start_time)).
Furthermore I advise you not to use functions in WHERE caluses. Modify your parameters so that their values represent the correct date. If you do this, the WHERE clause needs no function and the database engine calculates a better execution plan.
Good Luck 🙂
Best regards
Henning
CAST(datetime to DATE) is SARGable...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 24, 2016 at 3:20 am
Henning Rathjen (5/24/2016)
first I would try to useCONVERT(char(10),b.start_time,121)
instead of dateadd(dd, 0, datediff(dd, 0, b.start_time)).
String conversions are slower than the date functions for date manipulation, in my testing close on 3 times slower.
Old blog post: http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2016 at 3:29 am
Hi Gail,
thanks for feedback!
Greets
Henning
May 24, 2016 at 4:14 am
Thanks for the feedback everyone!
The tables structure I provided earlier was a simplified version as I believed it will be sufficient, turned out I was wrong. Full table structure as below:
1) ann_events_Tech_Details
CREATE TABLE [dbo].[ann_events_Tech_Details](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[events_id] [bigint] NOT NULL,
[ani] [nvarchar](20) NULL,
[dnis] [nvarchar](20) NULL,
[lang] [nvarchar](5) NULL,
[source] [nvarchar](50) NULL,
[call_flow_name] [nvarchar](50) NULL,
[start_time] [datetime] NULL,
[end_time] [datetime] NULL,
[tech_max_sn] [bit] NULL,
[tech_max_sm] [bit] NULL,
[tech_max_mobile] [bit] NULL,
[tech_hangup] [bit] NULL,
[tf_tech_home] [bit] NULL,
[tf_tech_reconnect] [bit] NULL,
[tf_tech_critical] [bit] NULL,
[tf_tech_del] [bit] NULL,
[tf_tech_business] [bit] NULL,
[tf_drop_tech_home] [bit] NULL,
[tf_drop_tech_reconnect] [bit] NULL,
[tf_drop_tech_critical] [bit] NULL,
[tf_drop_tech_del] [bit] NULL,
[tf_drop_tech_business] [bit] NULL,
[sel_tech_dialup] [bit] NULL,
[sel_tel_nodialtone] [bit] NULL,
[sel_tel_noisy] [bit] NULL,
[sel_tech_internet] [bit] NULL,
[sel_tel_csr] [bit] NULL,
[sel_tech_ckc_disconnect] [bit] NULL,
[sel_tech_ckc_transfer] [bit] NULL,
[sel_tech_tel] [bit] NULL,
[sel_tech_other] [bit] NULL,
[tech_sni_success] [bit] NULL,
[tech_sni_failure] [bit] NULL,
[tech_tos] [bit] NULL,
[tech_tt] [bit] NULL,
[tech_ckc] [bit] NULL,
[tech_tt_creation_success] [bit] NULL,
[tech_tt_creation_failure] [bit] NULL,
[tech_consumer] [bit] NULL,
[tech_business] [bit] NULL,
[tech_time_warranty] [bit] NULL,
[tech_dat] [bit] NULL,
[last_menu_selection] [nvarchar](200) NULL,
[service_number] [nvarchar](20) NULL,
[sn_invalid_count] [int] NULL,
[sn_success_count] [int] NULL,
[sn_timeout_count] [int] NULL,
[sn_error_count] [int] NULL,
[mobile_num] [nvarchar](20) NULL,
[tt_number] [nvarchar](20) NULL,
[tt_invalid_count] [int] NULL,
[tt_success_count] [int] NULL,
[tt_timeout_count] [int] NULL,
[tt_error_count] [int] NULL,
[ckc_invalid_count] [int] NULL,
[ckc_success_count] [int] NULL,
[ckc_timeout_count] [int] NULL,
[ckc_error_count] [int] NULL,
[sel_bill_statement] [bit] NULL,
[sel_bill_payment] [bit] NULL,
[sel_bill_transfer] [bit] NULL,
[sel_bill_paybill] [bit] NULL,
[tf_bill] [bit] NULL,
[tf_drop_bill] [bit] NULL,
[pbstat_invalid_count] [int] NULL,
[pbstat_success_count] [int] NULL,
[pbstat_timeout_count] [int] NULL,
[pbstat_error_count] [int] NULL,
[ciw] [bit] NULL,
[tf_pulse] [bit] NULL,
[tf_drop_pulse] [bit] NULL,
[tech_max_ss] [bit] NULL,
[ss_timeout] [bit] NULL,
[ss_icp] [bit] NULL,
[ss_nova] [bit] NULL,
[ss_tmgo] [bit] NULL,
[ss_wifi] [bit] NULL,
[ss_invalid] [bit] NULL,
[sel_inv_del] [bit] NULL,
[sel_inv_unifi] [bit] NULL,
[tf_wifi] [bit] NULL,
[tf_drop_wifi] [bit] NULL,
CONSTRAINT [PK_ann_events_Tech_Details] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_max_sn] DEFAULT ((0)) FOR [tech_max_sn]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_max_sm] DEFAULT ((0)) FOR [tech_max_sm]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF__ann_event__tech___18D6A699] DEFAULT ((0)) FOR [tech_max_mobile]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_hangup] DEFAULT ((0)) FOR [tech_hangup]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_tech_home] DEFAULT ((0)) FOR [tf_tech_home]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_tech_reconnect] DEFAULT ((0)) FOR [tf_tech_reconnect]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_tech_critical] DEFAULT ((0)) FOR [tf_tech_critical]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_tech_del] DEFAULT ((0)) FOR [tf_tech_del]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_tech_business] DEFAULT ((0)) FOR [tf_tech_business]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_drop_tech_home] DEFAULT ((0)) FOR [tf_drop_tech_home]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_drop_tech_reconnect] DEFAULT ((0)) FOR [tf_drop_tech_reconnect]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_drop_tech_critical] DEFAULT ((0)) FOR [tf_drop_tech_critical]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_drop_tech_del] DEFAULT ((0)) FOR [tf_drop_tech_del]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_drop_tech_business] DEFAULT ((0)) FOR [tf_drop_tech_business]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_tech_dialup] DEFAULT ((0)) FOR [sel_tech_dialup]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_tech_nodialtone] DEFAULT ((0)) FOR [sel_tel_nodialtone]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_tech_noisy] DEFAULT ((0)) FOR [sel_tel_noisy]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_tech_internet] DEFAULT ((0)) FOR [sel_tech_internet]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_tech_other] DEFAULT ((0)) FOR [sel_tel_csr]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF__ann_event__sel_t__15FA39EE] DEFAULT ((0)) FOR [sel_tech_tel]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF__ann_event__sel_t__16EE5E27] DEFAULT ((0)) FOR [sel_tech_other]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_sni_success] DEFAULT ((0)) FOR [tech_sni_success]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_sni_failure] DEFAULT ((0)) FOR [tech_sni_failure]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_tos] DEFAULT ((0)) FOR [tech_tos]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_tt] DEFAULT ((0)) FOR [tech_tt]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_ckc] DEFAULT ((0)) FOR [tech_ckc]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_tt_creation_success] DEFAULT ((0)) FOR [tech_tt_creation_success]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_tt_creation_failure] DEFAULT ((0)) FOR [tech_tt_creation_failure]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_consumer] DEFAULT ((0)) FOR [tech_consumer]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_business] DEFAULT ((0)) FOR [tech_business]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tech_time_warranty] DEFAULT ((0)) FOR [tech_time_warranty]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF__ann_event__tech___17E28260] DEFAULT ((0)) FOR [tech_dat]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sn_invalid_count] DEFAULT ((0)) FOR [sn_invalid_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sn_success_count] DEFAULT ((0)) FOR [sn_success_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sn_timeout_count] DEFAULT ((0)) FOR [sn_timeout_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sn_error_count] DEFAULT ((0)) FOR [sn_error_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tt_invalid_count] DEFAULT ((0)) FOR [tt_invalid_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tt_success_count] DEFAULT ((0)) FOR [tt_success_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tt_timeout_count] DEFAULT ((0)) FOR [tt_timeout_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tt_error_count] DEFAULT ((0)) FOR [tt_error_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_ckc_invalid_count] DEFAULT ((0)) FOR [ckc_invalid_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_ckc_success_count] DEFAULT ((0)) FOR [ckc_success_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_ckc_timeout_count] DEFAULT ((0)) FOR [ckc_timeout_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_ckc_error_count] DEFAULT ((0)) FOR [ckc_error_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_bill_statement] DEFAULT ((0)) FOR [sel_bill_statement]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_bill_payment] DEFAULT ((0)) FOR [sel_bill_payment]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_bill_transfer] DEFAULT ((0)) FOR [sel_bill_transfer]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_sel_bill_paybill] DEFAULT ((0)) FOR [sel_bill_paybill]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_bill] DEFAULT ((0)) FOR [tf_bill]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_drop_bill] DEFAULT ((0)) FOR [tf_drop_bill]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_pbstat_invalid_count] DEFAULT ((0)) FOR [pbstat_invalid_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_pbstat_success_count] DEFAULT ((0)) FOR [pbstat_success_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_pbstat_timeout_count] DEFAULT ((0)) FOR [pbstat_timeout_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_pbstat_error_count] DEFAULT ((0)) FOR [pbstat_error_count]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_ciw] DEFAULT ((0)) FOR [ciw]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_pulse] DEFAULT ((0)) FOR [tf_pulse]
GO
ALTER TABLE [dbo].[ann_events_Tech_Details] ADD CONSTRAINT [DF_ann_events_Tech_Details_tf_drop_pulse] DEFAULT ((0)) FOR [tf_drop_pulse]
GO
2) ann_ReportItem
CREATE TABLE [dbo].[ann_ReportItem](
[id] [int] IDENTITY(1,1) NOT NULL,
[report_id] [int] NULL,
[report_item] [nvarchar](100) NULL,
[report_item_id] [int] NULL,
[call_flow_name] [nvarchar](50) NULL,
[source] [nvarchar](50) NULL,
[remark] [nvarchar](500) NULL,
CONSTRAINT [PK_ann_ReportItem] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ann_ReportItem] WITH CHECK ADD CONSTRAINT [FK_ann_ReportItem_ann_Reports] FOREIGN KEY([report_id])
REFERENCES [dbo].[ann_Reports] ([id])
GO
ALTER TABLE [dbo].[ann_ReportItem] CHECK CONSTRAINT [FK_ann_ReportItem_ann_Reports]
GO
SQL Execution Plan is attached as well.
May 24, 2016 at 4:34 am
Don't you have any ordinary (nonclustered) indexes on these tables?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 24, 2016 at 4:36 am
jc85 (5/24/2016)
SQL Execution Plan is attached as well.
The actual plan please, not estimated.
Though, from the plan it looks like you need a couple of useful indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2016 at 6:17 am
Yes, both tables do not have non clustered index.
Will attach the actual plan what's I have access to my pc.
Viewing 15 posts - 1 through 15 (of 76 total)
You must be logged in to reply to this topic. Login to reply