ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,386 through 5,400 (of 10,143 total)

RE: Alphanumeric number generation

Jeff Moden (10/29/2012)
Eugene Elutin (10/29/2012)
Jeff Moden (10/27/2012)
I wonder how many 3 and 4 letter swear words such a proccess will actually spell out...
Actually it depends how you take it, at...
^{“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 29, 2012 at 6:37 am

#1553698
RE: Index scan on foreign key reference

That looks fine to me. Ensure statistics are up to date for indexes on Tab2, and check for an index seek in the CI of Tab2 when you insert 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 29, 2012 at 6:27 am

#1553694
RE: Index scan on foreign key reference

ioani (10/29/2012)
I have a table Tab1 with a column that is a foreign key to the primary key of table Tab2.
I have a stored procedure that selects some data 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

October 29, 2012 at 5:33 am

#1553682
RE: Query performance change

twoeyed (10/26/2012)
Thank you all for taking the time to reply and for your valuable inputs.
I have managed to reduce the execution to around 6 minutes through the ideas proposed, which...
^{“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 26, 2012 at 7:09 am

#1553149
RE: != NOT EQUAL TO Query

Comment out the filter you aren't sure about:
SELECT Db.dbase.name, Db.dbase.ID_Num, Db.dbase.ID_Type, Db.dbase.GroupName, Db.dbase.Region ...
^{“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 26, 2012 at 6:54 am

#1553139
RE: Creating a Non-Cluster Index

ksr39 (10/23/2012)
Hi Experts,
I need a small help in creating non cluster index on a table as we were facing a performance issue. The issue was in one of our production...
^{“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, 2012 at 7:23 am

#1552251
RE: Removing only contiguous/adjacent duplicate records from a rowset

EamonSQL (10/24/2012)
select from the table using the ROW_NUMBER() function ordering by id and partitioning by value.
Wrap in brackets giving you a derived table.
Run a delete command against the table and...
^{“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, 2012 at 6:49 am

#1552237
RE: Query performance change

Jeff's nailed it:
-- If *this* query returns any rows
SELECT 1
from Interface a
INNER JOIN AnotherDb.dbo.Trans b
ON (convert(varchar, a.TICKET_NUM) = b.TICKET_NUM and a.TXN_DT = b.TXN_DT)
where a.P_STATUS <> 'R'
-- and b.TICKET_NUM...
^{“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, 2012 at 5:54 am

#1552216
RE: Why does my yesterday query return no results?

Jeff Moden (10/23/2012)
ChrisM@Work (10/23/2012)
Yes - cast both sides as DATE
I don't believe so, Chris... no chance of getting an index seek that way.
That's what I thought too until recently.
^{“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, 2012 at 2:36 am

#1552165
RE: Why does my yesterday query return no results?

Jeff Moden (10/23/2012)
jerome.morris (10/23/2012)
It has a time stamp also and the cast gave me the error
Operand type clash: date is incompatible with int
Wait a minute now... are you saying that...
^{“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, 2012 at 2:34 am

#1552164
RE: Why does my yesterday query return no results?

Oops...
CAST(GETDATE() -1 AS DATE)
^{“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 23, 2012 at 7:57 am

#1551918
RE: Why does my yesterday query return no results?

jerome.morris (10/23/2012)
SELECT *from SLADB.dbo.DocketTB
where Docket_EngFinish >=dateadd(d,datediff(d,0, getdate()),-1) AND Docket_EngFinish<dateadd(d,datediff(d,0, getdate()),0)
This works, is there a better way ?
Thanks again Chris 🙂
Yes - cast both sides as DATE
where CAST(Docket_EngFinish AS DATE)...
^{“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 23, 2012 at 7:47 am

#1551909
RE: Why does my yesterday query return no results?

Try casting both sides of the comparison as date:
WHERE CAST(Docket_Date AS DATE) = CAST(GETDATE() AS DATE) - 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

October 23, 2012 at 7:23 am

#1551887
RE: help with difficult situation

o1548324 (10/13/2012)
haiao2000 thanks for your suggestion it ended up working. What i did was create a cursor and loaded it with each member_id then steped through the cursor and ran...
^{“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 13, 2012 at 3:21 pm

#1548920
RE: Char And int

-- A numeric column will sort numerically.
-- This column is character and will sort alphabetically
-- so you have to work around it. Here's one way.
;with cte as
(
select '1' as...
^{“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 13, 2012 at 2:20 am

#1548875

Viewing 15 posts - 5,386 through 5,400 (of 10,143 total)