ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,011 through 2,025 (of 10,143 total)

RE: Need help with a query that returns only rows that meet specific condition

ranjitdaljitmand (10/23/2015)
Hmm, I am slightly confused here because as per my understanding this wiil be a simple or rather a basic query to fetch those 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

October 23, 2015 at 1:52 am

#1835065
RE: Need help with a query that returns only rows that meet specific condition

Alan.B (10/22/2015)
Scott did my first solution (that one only scans the table once)...
Here's another way:
SELECT UniqID
FROM #SampleData
WHERE Code = 'ABC'
EXCEPT
SELECT UniqID
FROM #SampleData
WHERE Code <> 'ABC'
And another:
SELECT o.UniqID
FROM #SampleData o
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

October 23, 2015 at 1:31 am

#1835063
RE: SQL Loop HELP!

SELECT d.CODE, e.*
FROM (
SELECT CODE
FROM ##SENData
GROUP BY CODE
) d
CROSS JOIN (
SELECT
[Month] = GETDATE(),
Value = 0,
Measure = 'HAR-01'
) e
^{“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 22, 2015 at 8:53 am

#1834930
RE: View not performing well, resulting in memory insufficient errors

Sean Lange (10/22/2015)
Another highly likely issue for performance is the lack of normalization. You have to join to the same table over and over. If this was properly normalized 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 22, 2015 at 7:56 am

#1834904
RE: ?HELP?T_SQL LIKE CLAUSE

knockyo (10/22/2015)
Hi All,
In my column (FilePath) contains the values as below,
FILEPATH
---------------
\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P
\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122\207
\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C124\212
\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C127\252
If my SQL as below,
select * from TRFile where FilePath LIKE '%\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12%' order by FileName...
^{“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 22, 2015 at 4:51 am

#1834849
RE: Speed up following query (Second opinion needed)

Resender (10/22/2015)
I'm currently doing a 7AM to 4PM shift.
I might be missing something
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "x.Server" could not be bound.
Msg 4104, Level 16,...
^{“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 22, 2015 at 2:06 am

#1834829
RE: Speed up following query (Second opinion needed)

You must be in Yerp to be active at this ungodly hour.
We're homing in on a decent solution now. Try this:
SELECT
Year_ = YEAR(dg.Date_),
Month_ = MONTH(dg.Date_),
dg.[Server],
dg.Domain,
COUNTRY = MAX(drc.COUNTRY),
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 22, 2015 at 1:42 am

#1834824
RE: Speed up following query (Second opinion needed)

Resender (10/21/2015)
no problem,making errors is in human nature,I'm however at the end of my workday and I carpool to work so I don't have time to start the new 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

October 21, 2015 at 8:13 am

#1834684
RE: Speed up following query (Second opinion needed)

Resender (10/21/2015)
Execution results
--distinct
13:12
108054
--no distinct
17:19
4919706
Rapid glamps shows same data but a lot of duplicates which I suspected.
I've included the actual execution plans, so far I'm happy the time is more then...
^{“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, 2015 at 7:50 am

#1834674
RE: How to make query to calculate a dynamic formula ?

Sean Lange (10/21/2015)
gentong.bocor (10/17/2015)
Yes, that's actually my design is calculating the formula based on the value of each rows. I made it because the user can freely adding any rows...
^{“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, 2015 at 7:42 am

#1834666
RE: help with writing subquery

rash3554 (10/21/2015)
Hi there,
I need a little help writing the query
Create table test(CompanyID int, VendorID varchar(10),SalesinMilUSD numeric(10,4));
insert into test(1,1000,10.3);
insert into test(1,2000,99.3);
insert into test(2,2000,0.8);
insert into test(3,3000,12.9);
insert into test(3,2000,13.1);
insert into test(3,1000,12.9);
insert...
^{“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, 2015 at 6:53 am

#1834621
RE: Speed up following query (Second opinion needed)

Resender (10/21/2015)
Ok sorry for the late answer I did not have time yesterday to pursue this.
Here are the actual execution plans for both versions.
I have created 1 clustered index in...
^{“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, 2015 at 6:21 am

#1834606
RE: How to add a primary key for existing column in the table

jonas.gunnarsson 52434 (10/21/2015)
Also verify the current collation, if column is nvarchar.
But first verify duplicates
-- List duplicates for column
select A.<column_name>
from <table_name> as A
left outer join <table_name> 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 21, 2015 at 5:21 am

#1834579
RE: View not performing well, resulting in memory insufficient errors

Here's that view formatted a little for readability:
CREATE VIEW [dbo].[VW_DTIRIS_Branck_Mngr_Hierarchy]
AS
SELECT --DISTINCT TOP (100) PERCENT
p.OrgUnitManager, p.OrgUnitID, p.OrgUnitName, p.ParentID AS CHILD1_ParentID,
C.OrgUnitManager AS CHILD1_OrgUnitManager, C.OrgUnitID AS CHILD1_OrgUnitID, C.OrgUnitName AS CHILD1_OrgUnitName,
C2.OrgUnitManager 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 21, 2015 at 2:14 am

#1834547
RE: Database Tuning Advisor crashes on complex query

Lynn Pettis (10/19/2015)
Here is the update query initially post in a slightly more readable format:
UPDATE SERVICE_REQUEST
SET SERVICE_REQUEST_STATUS = 'PrintedOnPullReport'
,LAST_PRINTED_ON_REPORT_DATE = '2015-10-15 00:00:00'
,LAST_MOD_DATE_TIME = '2015-10-15 06:10:03.223'
,LAST_MODIFIER = 60417
,PRINT_REQUEST_ID = 69432
FROM SERVICE_REQUEST...
^{“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, 2015 at 1:50 am

#1834541

Viewing 15 posts - 2,011 through 2,025 (of 10,143 total)