ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,256 through 3,270 (of 10,143 total)

RE: Need to Optimize Update Query

Your query has an inner join between tables STG.personattribute (stg) and GDW.personattribute (tar)
which includes this predicate: Tar.AttributeID = stg.AttributeID.
The WHERE clause includes this predicate: ISNULL(Tar.AttributeID, 0) != ISNULL(stg.AttributeID, 0),
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

June 16, 2014 at 3:07 am

#1721862
RE: Sproc fails to return data to app after a couple of weeks

There are a few hints in here which might help you on your way:
ALTER PROCEDURE [dbo].[TR_SearchTests_Counter_v2]
@REGISTER_IDint= null,
@PART_NUMBERnvarchar(50)= null,
@PHASE_NUMBERnvarchar(10)= null,
@TEST_NUMBERnvarchar(20) = null,
@PROCESS_ORDERnvarchar(50) = null,
@IS_VOIDbit= null,
@REGISTERED_FROMsmalldatetime=...
^{“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 12, 2014 at 9:51 am

#1721140
RE: Need help to finding pattern

DwainC wrote this article [/url]a couple of years ago which might offer you some ideas.
^{“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 12, 2014 at 7:00 am

#1721051
RE: Would like some feedback on contractors as employers

J Livingston SQL (6/11/2014)
In a similar vein .....
what's the current UK market like?
Strongly seasonal. Two months ago I was getting two or three phone calls each day, now it's more...
^{“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 12, 2014 at 1:42 am

#1720944
RE: script to select certain fields

Sean Lange (6/11/2014)
SQLSteve (6/11/2014)
Sean, the query only posted 1 entry back to me from the duplicate. Chris' posted both enrties, so I can now see all of the entries rather...
^{“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 11, 2014 at 9:48 am

#1720807
RE: script to select certain fields

SELECT fullname, attrib3, num_occurrences
FROM (
SELECT
fullname,
attrib3,
num_occurrences = COUNT(*) OVER(PARTITION BY fullname)
FROM userdb
WHERE companyname LIKE 'carpet%'
) d
WHERE num_occurrences > 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 11, 2014 at 9:12 am

#1720783
RE: update row always has results but still doesn't work

pk2dpvp (6/11/2014)
well,
I fixed the problem
though I do not know why it got fixed...
what I did:
I changed ever A to SP and every SP to A (Turned around the script so...
^{“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 11, 2014 at 8:09 am

#1720760
RE: Join on different data types

sql-lover (6/11/2014)
ChrisM@Work (6/11/2014)
CREATE TABLE #temp (colint INT, colvarchar VARCHAR(10), colcomputed AS CAST(colvarchar AS INT))
INSERT INTO #temp (colint, colvarchar) SELECT 1, '10'
SELECT * FROM #temp
CREATE INDEX ix_colcomputed ON #temp (colcomputed)
SELECT colcomputed...
^{“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 11, 2014 at 7:35 am

#1720744
RE: Join on different data types

CREATE TABLE #temp (colint INT, colvarchar VARCHAR(10), colcomputed AS CAST(colvarchar AS INT))
INSERT INTO #temp (colint, colvarchar) SELECT 1, '10'
SELECT * FROM #temp
CREATE INDEX ix_colcomputed ON #temp (colcomputed)
SELECT colcomputed FROM #temp...
^{“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 11, 2014 at 7:05 am

#1720731
RE: update row always has results but still doesn't work

The two large data blocks which you posted earlier are slowing down access to this thread - can I ask you to shorten each block to say ten rows please?...
^{“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 11, 2014 at 6:41 am

#1720725
RE: update row always has results but still doesn't work

pk2dpvp (6/11/2014)
...
(1960 row(s) affected)
(1960 row(s) affected)
(1960 row(s) affected)
it returned utter chaos :p
Not quite. Your results are exactly consistent with running this:
-- Query 1
SELECT
a.omschrijving, sp.omschrijving,
--a.verkoopprijs, sp.verkoopprijs,
a.gewijzigd, getDate()
FROM artikelen a
INNER JOIN...
^{“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 11, 2014 at 5:44 am

#1720713
RE: update row always has results but still doesn't work

pk2dpvp (6/11/2014)
so according to what you are saying, the problem in my script is the float point
I've tried to run my script without any float's (Just the description, which 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

June 11, 2014 at 5:01 am

#1720703
RE: update row always has results but still doesn't work

Thanks.
Here's an article which explains what's happening and offers a workaround.
^{“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 11, 2014 at 4:29 am

#1720698
RE: update row always has results but still doesn't work

Select one of the tables in SSMS.
Right-click on column [verkoopprijs] to raise the property sheet. Record the values for [Length] and [Numeric Precision].
Do the same with the other table, post...
^{“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 11, 2014 at 3:51 am

#1720689
RE: update row always has results but still doesn't work

Select one of the tables in Object Explorer in SSMS.
Right-click on it, from the popup menu select "Script Table as", then "Create to" then "New Query Editor Window".
Do 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 11, 2014 at 3:20 am

#1720682

Viewing 15 posts - 3,256 through 3,270 (of 10,143 total)