ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,846 through 4,860 (of 10,143 total)

RE: SQL query help

mamzy.rahman (4/11/2013)
problem is tho were i have put 1 2 3 and z there jus examples the actual data is completely different and would be impossible for me to do...
^{“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

April 11, 2013 at 4:24 am

#1605515
RE: SQL query help

In the little query I wrote above, change #Sample to aims.
Just this query, not the sample data generator:
-- solution
SELECT COUNT(L01)
FROM #Sample s
WHERE NOT EXISTS (
SELECT 1
FROM #Sample si
WHERE...
^{“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

April 11, 2013 at 4:14 am

#1605511
RE: SQL query help

mamzy.rahman (4/11/2013)
i cant create just query 🙁
What is the name of the table you are using?
^{“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

April 11, 2013 at 4:08 am

#1605508
RE: SQL query help

mamzy.rahman (4/11/2013)
hi thanks for your efforts but i cannot drop the tables is there a way of doing it without altering any of the tables
-- create some sample data 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

April 11, 2013 at 4:05 am

#1605505
RE: SQL query help

-- create some sample data to test against
DROP TABLE #Sample
CREATE TABLE #Sample (L01 INT, a09 CHAR(1))
INSERT INTO #Sample (L01, a09)
SELECT 123, '1' UNION ALL
SELECT 123, '2' UNION ALL
SELECT 123, 'z'...
^{“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

April 11, 2013 at 3:58 am

#1605503
RE: Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation

-- Here's the last change.
USE [Build1]
SELECT
td.TYPE_NAME
,i.DOC_NAME
,i.DOC
,i.SEC
,i.VER
,r.JUSTIFICATION
FROM TBL_INSTANCES i
INNER JOIN TBL_TYPE_DEF td
ON i.TYPE_ID = td.TYPE_ID
LEFT JOIN TBL_RELATIONSHIPS r
ON i.DOC = r.PARENT_DOC
AND i.SEC = r.PARENT_SEC
AND i.VER =...
^{“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

April 11, 2013 at 3:28 am

#1605491
RE: Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation

Thanks to Lynn & Sean for the formatted query:
-- code rewrites are iterative in nature. You make a change or two,
-- test thoroughly, amend if necessary, then make another change.
--...
^{“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

April 11, 2013 at 3:16 am

#1605488
RE: Ignoring timeout in stored procedure

Kurtman (4/10/2013)
Thanks for the idea. I did use try...catch blocks with transactions and tested for the transaction state, but I crashed into DTC with so many linked servers. ...
^{“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

April 11, 2013 at 1:17 am

#1605480
RE: Which of the two queries is better ???

T.Ashish (4/10/2013)
It comes from Java Code, like ......where Site_id = :lablesiteid....
-- Query 2
SELECT
TOP 100 comp_id,
sup_code,
sup_name,
sup_ac_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

April 11, 2013 at 1:07 am

#1605479
RE: In linked server, which server will process data?

adonetok (4/10/2013)
In ServerA, I created a linked server to ServerB.
In ServerA, there is a query like below.
select * from [ServerB].order.dbo.allorder where city='london'
Question:
Which server will process data?
1) processing...
^{“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

April 10, 2013 at 9:31 am

#1605293
RE: Which of the two queries is better ???

T.Ashish (4/10/2013)
Hard coded value is coming from SITE_AC table. Now it is not there because I had removed some rows from script to reduce the size of attachment. you can...
^{“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

April 10, 2013 at 9:30 am

#1605291
RE: How to check the index fragmentation on a schema binding view

Same as any other index:
SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N'LIMITED') AS ps
INNER JOIN sys.indexes...
^{“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

April 10, 2013 at 8:20 am

#1605249
RE: Developers track changes

Krasavita (4/10/2013)
Yes,this is just for tables,what about any changes on database?
Default trace[/url] could provide most of what you're looking for.
^{“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

April 10, 2013 at 7:49 am

#1605230
RE: Query - Nested Loop Join

sqldba_newbie (4/10/2013)
ChrisM@Work (4/10/2013)
sqldba_newbie (4/9/2013)
In below query , in the exec plan i see nested loop join on TBFE table. This is executed for 25,000 times. I could force a hash...
^{“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

April 10, 2013 at 6:55 am

#1605198
RE: Get week ending date given the week number

FollowingSaturday = DATEADD(day,6-DATEDIFF(day,6,Today)%7,Today)
-- example
;WITH ATableOfDates AS (
SELECT TOP(300)
Today = DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-150,CAST(GETDATE() AS DATE))
FROM sys.columns
)
SELECT
Today,
[DayOfWeek] = DATENAME(weekday,Today),
FollowingSaturday = DATEADD(day,6-DATEDIFF(day,6,Today)%7,Today)
FROM ATableOfDates
^{“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

April 10, 2013 at 6:05 am

#1605176

Viewing 15 posts - 4,846 through 4,860 (of 10,143 total)