ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,331 through 3,345 (of 10,143 total)

RE: WHERE Clause comparing more than 2 columns

Here's another guess;
DROP TABLE #Equipment;CREATE TABLE #Equipment ([Equipment ID] VARCHAR(10), [Equipment Branch] VARCHAR(20))
INSERT INTO #Equipment ([Equipment ID], [Equipment Branch])
VALUES ('CAG001','BRISBANE'),('CAG002','TOWNSVILLE'),('CAG003','PERTH'),('CAG004','BRISBANE'),('CAG005','PERTH'),('CAG006','PERTH')
DROP TABLE #Asset;CREATE TABLE #Asset ([Equipment ID] VARCHAR(10), [Asset Branch] VARCHAR(20))
INSERT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 16, 2014 at 9:57 am

#1714249
RE: Where clause on multiple columns?

Kevlarmpowered (5/16/2014)
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns
FROM table
WHERE (column5 = '1'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 16, 2014 at 8:39 am

#1714214
RE: Large Insert taking more than 20Hrs on DW env

The only difference I can spot between the two UNIONed queries is this filter:
prev_stat.DW_POLICY_STATUS IN (7) or prev_stat.DW_POLICY_STATUS IN (8)
If this is the case, then combine the two queries:
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 16, 2014 at 7:16 am

#1714173
RE: Compare Comma seprated values of two table in sql server 2005

-- set up some sample data
DROP TABLE #t1
CREATE TABLE #t1 (Table1ID INT IDENTITY(1,1) PRIMARY KEY, DenormalisedString VARCHAR(8000))
INSERT INTO #t1 (DenormalisedString) VALUES ('Red,Green,Blue'),('Red,Green'),('Red,Blue'),('Green,Blue')
DROP TABLE #t2
CREATE TABLE #t2 (Table2ID INT IDENTITY(1,1) PRIMARY...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 16, 2014 at 4:59 am

#1714140
RE: T-SQL query with loop ?

MathewK (5/14/2014)
Thanks much Chris. You are awesome! 🙂 Thanks for working through my unclear specs and data. My apologies.
Your solution works well on larger sample size.
Could you mention the changes...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 16, 2014 at 2:20 am

#1714114
RE: Mysterious performance issue when joining a view

Implicit conversions in joins:
[Incentives].[dbo].[tblVehicleTurnoverSummary].[ModelCode]=CONVERT_IMPLICIT(nvarchar(3),[Incentives].[dbo].[tblInvoiceNotRegisteredHeader].[ModelCode],0) AND [Incentives].[dbo].[tblVehicleTurnoverSummary].[Chassis]=CONVERT_IMPLICIT(nvarchar(8),[Incentives].[dbo].[tblInvoiceNotRegisteredHeader].[Chassis],0)
?Referenced twice.
[Sales].[dbo].[tblRegHist].[PK_tblRegHist_1__17] has no clustered index and a RID lookup. Add an appropriate clustered index and a covering index.
As Sean pointed out, joining views to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 9:47 am

#1713912
RE: Query performance (Avoid Cluster index scan and Lazy spool)

This query
SELECT COUNTRY_CODE_ALPHA2,
e.*
into #temp3
FROM SECURITY_EVENTS_STG_TEST e
INNER JOIN dbo.IP2COUNTRY_LOOKUP c
ON e.SOURCE_ADDRESS_INT BETWEEN c.[START] AND c.[END]
With a covering index on SECURITY_EVENTS_STG_TEST.SOURCE_ADDRESS_INT
Results in seeks with no (relatively expensive) residual predicate:
Seek Keys[1]:
Start: [dbo].[SECURITY_EVENTS_STG_TEST].SOURCE_ADDRESS_INT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 8:17 am

#1713866
RE: Query performance (Avoid Cluster index scan and Lazy spool)

set statistics io, time on
PRINT 'Original query'
SELECT
(SELECT COUNTRY_CODE_ALPHA2
FROM dbo.IP2COUNTRY_LOOKUP C
WHERE START = (SELECT max(START)
FROM DBO.IP2COUNTRY_LOOKUP
WHERE START <= E.SOURCE_ADDRESS_INT)
AND "END" >= E.SOURCE_ADDRESS_INT) AS SRC_COUNTRY_CODE_ALPHA2,
E.*
INTO #Temp1
FROM SECURITY_EVENTS_STG_TEST E
print '====================================================================================='
PRINT 'Possible replacement...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 8:01 am

#1713859
RE: Query performance (Avoid Cluster index scan and Lazy spool)

bas_vdl (5/15/2014)
spaghettidba (5/15/2014)
You cannot avoid the scan: you're asking for the whole SECURITY_EVENTS_STG_TEST table (no filter predicates).
As far as the index spool is concerned, I have no idea what it's...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 5:16 am

#1713793
RE: Retrieve data from table based on condition

Can you please post the CREATE TABLE script for the source table, including any constraints/defaults/indexes? Cheers.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 5:04 am

#1713787
RE: Query performance (Avoid Cluster index scan and Lazy spool)

bas_vdl (5/15/2014)
Thanks for the quick reply. Your query is slightly quicker, it takes between 15 and 20 seconds on my dataset...
Are there any other query construction to lookup the country...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 4:27 am

#1713768
RE: Query performance (Avoid Cluster index scan and Lazy spool)

spaghettidba (5/15/2014)
You cannot avoid the scan: you're asking for the whole SECURITY_EVENTS_STG_TEST table (no filter predicates).
As far as the index spool is concerned, I have no idea what it's trying...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 4:13 am

#1713761
RE: query help needed

SQL Server will join tables in whatever order results in the lowest-cost plan. Join order doesn’t matter. The order of ON clauses matters – and can be manipulated to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 2:58 am

#1713738
RE: Conver NVARCHAR to INT

Viky123 (5/15/2014)
Declare @A AS NVARCHAR(16)
SET @A='1'
Select Case WHEN ISNUMERIC(@A)=1 THEN Cast(@A AS int)
ELSE @A
END
This code is working fine but,
Why below code give me error also please...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 15, 2014 at 2:19 am

#1713728
RE: Select * if null passed else by INT

ScottPletcher (5/14/2014)
ChrisM@Work (5/14/2014)The execution plans for those two queries is likely to be completely different. If you were to write the query as a single "catch-all" query, it would be...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
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 14, 2014 at 9:10 am

#1713517

Viewing 15 posts - 3,331 through 3,345 (of 10,143 total)