ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,541 through 6,555 (of 10,143 total)

RE: Query Optimization using Indexes

adlakha.22 (10/24/2011)
Thanks for Reply Chris
I have executed all the 3 queries
and got these results
1st qry took:1sec
2nd-2 sec
3rd-2 sec
as u were expected ,here is improvement in execution time for 2nd Qry
Very...
^{“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

October 24, 2011 at 2:57 am

#1398549
RE: Need Help Creating a View

Have a look at the following query. I've left all the workings in there to make it easier to figure out what's going on.
DROP TABLE #UserAbsences
CREATE TABLE #UserAbsences (UserID 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

October 24, 2011 at 2:42 am

#1398543
RE: Query Optimization using Indexes

ChrisM@Work (10/20/2011)
... Here are three test queries. I need to know how long each one takes to run, then I need the execution plan for all three run together.
-- Test...
^{“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

October 24, 2011 at 2:12 am

#1398538
RE: Query Optimization using Indexes

ChrisM@Work (10/20/2011)
Well it's clear that a huge covering index would improve the performance of this query but the cost of maintaining it would be considerable. Here's a compromise.
CREATE NONCLUSTERED INDEX...
^{“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

October 24, 2011 at 2:11 am

#1398537
RE: Query Optimization using Indexes

adlakha.22 (10/24/2011)
Hi Chris,
I was trying to use Suggestion given in your last post,
Could u plz guide me further...how to move on that Qry..
Sure. Are you on UK time by any...
^{“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

October 24, 2011 at 2:08 am

#1398534
RE: float to varchar

sql_novice_2007 (10/24/2011)
Does anyone has any ideas of updating this value as a number into varchar field by not converting into 7.77888+e009
Thanks.
Look up the STR() function in Books Online. The STR()...
^{“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

October 24, 2011 at 2:05 am

#1398532
RE: float to varchar

sql_novice_2007 (10/21/2011)
I have fax number in two tables. I need to update data from tableA to tableB.
fax_number column in tableA is of varchar and in tableB it is float.
so when...
^{“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

October 21, 2011 at 9:25 am

#1398065
RE: How to generate all Possible Alphabit pattern in a sequence?

SQLRNNR (10/20/2011)
Wow - nice work Chris
Gosh, thanks Jason. I have to admit though, I don't know why the Sort is displayed as such a high cost in the plan when...
^{“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

October 21, 2011 at 9:17 am

#1398059
RE: string manipulation or cte

SELECT
String,
[from Facility Id] = SUBSTRING(d.String,item1.Start,CHARINDEX('Patient Id:',String,0)-item1.Start),
[from Patient Id] = SUBSTRING(d.String,item2.Start,CHARINDEX('PHPatId:',String,0)-item2.Start),
[from PHPatId] = SUBSTRING(d.String,item3.Start,CHARINDEX('with Facility Id:',String,item3.Start)-item3.Start),
[to Facility Id] = SUBSTRING(d.String,item4.Start,CHARINDEX('Patient Id:',String,item4.Start)-item4.Start),
[to Patient Id] = SUBSTRING(d.String,item5.Start,CHARINDEX('PHPatId:',String,item5.Start)-item5.Start),
[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

October 21, 2011 at 8:42 am

#1398021
RE: string manipulation or cte

timscronin (10/21/2011)
Not fixed, the patient id chan be from 3-12 characters long
Extracting "words" separated by delimiters - even heterogenous ones - from strings is TSQL bread-and-butter. The hard part is...
^{“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

October 21, 2011 at 7:58 am

#1397982
RE: LTRIM,RTRIM

daveriya (10/21/2011)
so it gives orderid values with no spaces on left or right ?
or it just says orderid should be blank.plz confirm me
Try experimenting with the sample code which Todd...
^{“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

October 21, 2011 at 7:52 am

#1397976
RE: Sort Data ??? different one !!!

karthikeyan-444867 (10/21/2011)
ORDER BY LEFT(Name,1) COLLATE SQL_Latin1_General_CP1_CS_AS DESC, Name
I still don't understand the working mechanism of these queries.
Have a gander at BOL COLLATE section. When you specify ORDER BY in a...
^{“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

October 21, 2011 at 5:59 am

#1397891
RE: Kill the windows user

vivekinmadar (10/21/2011)
I guess the problem is that certain users are running large inefficent queries that take a lot of cpu and time to complete, is this correct?
This correct ....
Better 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

October 21, 2011 at 5:49 am

#1397880
RE: Sort Data ??? different one !!!

SELECT *
FROM (
select Name = 'anbu' union ALL
select 'balu' union ALL
select 'Azhagu' union ALL
select 'Chitra' union ALL
select 'Babu' union ALL
select 'chennai' union ALL
select 'Bama') d
ORDER BY LEFT(Name,1) COLLATE SQL_Latin1_General_CP1_CS_AS DESC,...
^{“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

October 21, 2011 at 5:14 am

#1397861
RE: Join using Charindex -- there's got to be a better way?

Hi Peter, I'm running out of time today but here's some notes.
The entire cross apply bit can - and should - be replaced by a string splitter function. This is...
^{“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

October 20, 2011 at 10:13 am

#1397524

Viewing 15 posts - 6,541 through 6,555 (of 10,143 total)