ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,531 through 4,545 (of 10,143 total)

RE: Performance issue imbricate loop

Phil Parkin (6/6/2013)
ChrisM@Work (6/6/2013)
ERIC CRUDELI (6/6/2013)
Jesus !!!
He's gone home, there's just us geeks eating fish sandwiches and slugging Bordeaux.
--
Chris, reading this has made me feel queasy :sick: :hehe:
Comment removed, may...
^{“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

June 6, 2013 at 6:27 am

#1621701
RE: Issue while joining two tables with no unique columns

DROP TABLE #Graph_Range
CREATE TABLE #Graph_Range (Start_Range DECIMAL (5,2), End_Range DECIMAL (5,2))
INSERT INTO #Graph_Range (Start_Range, End_Range)
SELECT 0.10, 0.20 UNION ALL
SELECT 0.20, 0.30 UNION ALL
SELECT 0.30, 0.40
DROP TABLE...
^{“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

June 6, 2013 at 6:25 am

#1621700
RE: Inline Table Valued Function

This simple sample should get you started. Have a play, explain if it doesn't meet your requirements.
CREATE FUNCTION [dbo].[MyFirstITVF]
(
@StartNum INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT d.MyInt, x.MyString
FROM...
^{“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

June 6, 2013 at 6:10 am

#1621693
RE: Performance issue imbricate loop

ERIC CRUDELI (6/6/2013)
Hello,
I just found the problem just before to get your email.
The problen is in clause WHERE with P.[pat_nom] IS NOT NULL. If I remove it the reponse time...
^{“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

June 6, 2013 at 5:56 am

#1621686
RE: Performance issue imbricate loop

ERIC CRUDELI (6/6/2013)
Hello,
I tried this :
DECLARE @p__linq__0 varchar(25),
@p__linq__1 varchar(9),
@p__linq__2 varchar(25),
@p__linq__3 varchar(9)
SET @p__linq__0=N'ARD%'
SET @p__linq__1='CHU-LYON'
SET @p__linq__2=N'REI%'
SET @p__linq__3='CHU-LYON'
SELECT
P.pat_nom,
P.pat_site_code,
P.pat_nom_naissance,
P.pat_id,
L.[lnai_id] AS [lnai_id1],
L.[lnai_cp] AS [lnai_cp],
L.[lnai_ville] AS [lnai_ville],
L.[lnai_pays_id] AS [lnai_pays_id]
FROM [noyau].[PATIENT] P
LEFT...
^{“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

June 6, 2013 at 5:33 am

#1621674
RE: Performance issue imbricate loop

There's still a huge memory grant :ermm:
Try this very simplified version:
SELECT
pat_nom, pat_site_code, pat_nom_naissance, pat_id
FROM [noyau].[PATIENT]
WHERE ([pat_nom] LIKE @p__linq__0 ESCAPE '~')
AND ([pat_site_code] = @p__linq__1)
--AND ([Extent1].[pat_site_code] =...
^{“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

June 6, 2013 at 4:24 am

#1621662
RE: Performance issue imbricate loop

Also, you should match the datatype of your variables to your column datatypes:
DECLARE
@p__linq__0 nvarchar(?), -- pat_nom
@p__linq__1 varchar(?), -- pat_site_code
@p__linq__2 nvarchar(?), -- pat_nom_naissance
@p__linq__3 varchar(?) -- pat_site_code...
^{“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

June 6, 2013 at 3:35 am

#1621653
RE: Performance issue imbricate loop

ERIC CRUDELI (6/6/2013)
Jesus !!!
He's gone home, there's just us geeks eating fish sandwiches and slugging Bordeaux.
The existing index isn't well matched to this query - you're getting an index scan...
^{“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

June 6, 2013 at 3:09 am

#1621650
RE: Performance issue imbricate loop

ERIC CRUDELI (6/6/2013)
Query plan see attachment
No, not a picture of it - that's like ordering a taxi to the airport and someone turning up with a picture of a taxi....
^{“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

June 6, 2013 at 2:47 am

#1621640
RE: Performance issue imbricate loop

ERIC CRUDELI (6/6/2013)
Hello,
In this case, I return one line but anyway if the query returns one line or more the duration and CPU keep the same value.
Cheers,
Eric
Can you post the...
^{“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

June 6, 2013 at 2:22 am

#1621631
RE: Today's Random Word!

opc.three (6/5/2013)
SQLRNNR (6/5/2013)
Revenant (6/5/2013)
crookj (6/5/2013)
SQLRNNR (6/5/2013)
roach
La Cucaracha
Pancho Villa
Sombrero
Ushanka
Ushuaia
^{“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

June 6, 2013 at 1:28 am

#1621614
RE: Performance issue imbricate loop

You can make a significant improvement to the readability of the query by eliminating the unnecessary subselect, removing unnecessary column aliases and using sensible table aliases:
SELECT
p.[pat_id],
p.[pat_situation],
p.[pat_ancien_id],
p.[pat_portail_id],...
^{“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

June 6, 2013 at 1:21 am

#1621608
RE: Are the posted questions getting worse?

Stefan Krzywicki (6/5/2013)
rodjkidd (6/5/2013)
Stefan Krzywicki (6/5/2013)
rodjkidd (6/5/2013)
Wow looked as though I missed some thread activity last night while seeing Elvis Costello in concert...
On the plus side at current job, they...
^{“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

June 5, 2013 at 9:40 am

#1621392
RE: Dynamic SQL Giving 'Name not a valid identifier error'

GilaMonster (6/5/2013)
Please note: 4 year old answered thread.
Outstanding work for one so young 😀
^{“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

June 5, 2013 at 6:06 am

#1621311
RE: Dynamic SQL Giving 'Name not a valid identifier error'

softtesting2012 (6/5/2013)
Invalid: exec @sqlQuery
Valid: exec (@sqlQuery)
Not necessarily so. Read Gail's post above.
^{“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

June 5, 2013 at 5:45 am

#1621299

Viewing 15 posts - 4,531 through 4,545 (of 10,143 total)