ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,546 through 4,560 (of 10,143 total)

RE: insert records into table which record insert wrong that record only rollback remains insert

Try looking at this problem differently:
You want to insert some rows into a table [emp]. Any rows which will fail to insert into [emp] (e.g. duplicate key), should be inserted...
^{“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:21 am

#1621295
RE: Execution plans

m.rajesh.uk (6/5/2013)
i was given task identify long running queries and problematic queries.. but in the reults i got the above queries and none of the involve complex 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 5, 2013 at 4:59 am

#1621282
RE: help with query

Hi Daniel and welcome to SSC. Thanks for doing an excellent job of posting up ddl.
danielb27 (6/5/2013)
...some of your products are not selling, you would like a list of...
^{“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 2:29 am

#1621217
RE: Mistery never ending query

ricardo_chicas (6/4/2013)
not an stats issue
There's no evidence to support anything else. Lowell's explanation seems the most likely.
^{“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 4, 2013 at 9:36 am

#1621025
RE: Mistery never ending query

ricardo_chicas (6/4/2013)
I just solved the issue by adding a new index ( not necessary since I was using the clustered one)
the query started working, then I removed the new 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

June 4, 2013 at 8:22 am

#1620997
RE: Mistery never ending query

ricardo_chicas (6/3/2013)
if I remove field 2 from the equation it will work just fine
Any ideas?
An actual plan from the whole original query with "field 2" commented out as you...
^{“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 4, 2013 at 7:54 am

#1620984
RE: Mistery never ending query

ricardo_chicas (6/4/2013)
All
I can't even generate the exec plan. It never ends, and none is using that table since it is a copy of the original
And none is blocking that spod...
^{“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 4, 2013 at 6:56 am

#1620938
RE: MY query is procedure is not working?

Kingston Dhasian (6/4/2013)
ChrisM@Work (6/4/2013)
ALTER PROCEDURE update_use ( @name varchar(20), @class nvarchar(30) ) AS UPDATE grts SET class = @class WHERE name = @name IF @@ROWCOUNT = 0 INSERT INTO grts (name, class) VALUES (@name, @class)
Wow. This is 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 4, 2013 at 6:30 am

#1620916
RE: Mistery never ending query

ricardo_chicas (6/4/2013)
All
Thanks but you are missing the point. The original query was something much bigger
I used a top 1 just to show you that there is something wrong with one...
^{“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 4, 2013 at 6:10 am

#1620909
RE: MY query is procedure is not working?

ALTER PROCEDURE update_use
(
@name varchar(20),
@class nvarchar(30)
)
AS
UPDATE grts SET class = @class WHERE name = @name
IF @@ROWCOUNT = 0
INSERT INTO grts (name, class) VALUES (@name, @class)
^{“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 4, 2013 at 6:08 am

#1620908
RE: Join Problem

immaduddinahmed (6/4/2013)
...and this is my data of shift Table
CompanyID--1
BranchID--2
ShiftID--12
ShiftName--G
LongName--GENERAL SHIFT (09 HRS)
Stype--null
timein--1/23/2013 9:00:00 AM
Timeout--1/23/2013 6:00:00 PM
Ltime--1/23/2013 9:16:00 AM
HdTime--1/23/2013 2:00:00 PM
Night--0
TotalTime--6/3/2013 9:00:00 AM
...
You are more likely to get effective help if...
^{“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 4, 2013 at 2:19 am

#1620818
RE: Mistery never ending query

ricardo_chicas (6/3/2013)
Hello all
I have a big machine with 192gb, 16 cpus, and sql server 2012 RTM in it
I am trying to run this query
Select top 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

June 4, 2013 at 1:58 am

#1620809
RE: Join to Calendar Table

You're welcome.
Can we see your final query?
^{“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 4, 2013 at 1:00 am

#1620790
RE: query to search

Instead of writing your second query like this:
vounum IN ('BC201203000073',
'BC201204000001',
'BC201204000002',
'BC201205000001',
'BC201206000001',
'BC201206000002')
write it like this:
vounum IN (
SELECT a.vounum
FROM actrna as a
INNER JOIN acinf as b ON a.comcod=b.comcod AND a.actcode=b.actcode
INNER JOIN sirinf 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

June 4, 2013 at 12:59 am

#1620789
RE: Join to Calendar Table

Something like this?
SELECT LoadsOfStuff
FROM AnotherTable a
INNER JOIN [dbo].[Calendar] c
ON a.aDate BETWEEN c.FirstDateOfWeek AND c.LastDateOfWeek
AND c.CalendarDate = CAST(GETDATE() 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

June 3, 2013 at 9:32 am

#1620620

Viewing 15 posts - 4,546 through 4,560 (of 10,143 total)