ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,296 through 2,310 (of 10,143 total)

RE: please help me: select is verry slow from table 5 milion record

mrtran0404 (8/10/2015)
Sean Lange (8/10/2015)
mrtran0404 (8/10/2015)
Sean Lange (8/10/2015)
ChrisM@Work (8/10/2015)
John Mitchell-245523 (8/10/2015)
You're selecting every row from the table, so it's not going to be all that quick. Get rid of your...
^{“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

August 10, 2015 at 7:37 am

#1818551
RE: OFFSET is not accepting start index

Have you checked the data by eye to see if it's actually going to work?
create table #inv(
InvoiceNo varchar(20),
CustomerPo varchar(20),
InvoiceDateTime datetime
);
insert into #inv
select
Col1,col2,col3
...
^{“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

August 10, 2015 at 7:24 am

#1818541
RE: please help me: select is verry slow from table 5 milion record

-- Rewrite your inner query to use SARGable predicates.
-- Better still, consider a rewrite: build the query up as a string including only
-- the search criteria which will be used....
^{“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

August 10, 2015 at 5:05 am

#1818482
RE: please help me: select is verry slow from table 5 milion record

mrtran0404 (8/10/2015)
no i select folow value of variable when i call storeprocedure and then it exec this command to return result to me 😎
The query you have posted is nonsense,...
^{“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

August 10, 2015 at 2:32 am

#1818452
RE: please help me: select is verry slow from table 5 milion record

John Mitchell-245523 (8/10/2015)
You're selecting every row from the table, so it's not going to be all that quick. Get rid of your whole WHERE clause - it's just noise....
^{“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

August 10, 2015 at 2:23 am

#1818450
RE: please help me: select is verry slow from table 5 milion record

mrtran0404 (8/9/2015)
Dear all,
i having isue is when i select data form my table took more than 10 seconds to get result,
there are solutions that can help me get...
^{“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

August 10, 2015 at 2:17 am

#1818447
RE: OFFSET is not accepting start index

declare @StartRow int = 10, @EndRow int = 10
drop table #Temp;
;WITH
n1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n = 0 FROM n1, n1 n),
iTally 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

August 7, 2015 at 10:19 am

#1818201
RE: Tuning a Stored Procedure

Grant Fritchey (8/7/2015)
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.
Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE...
^{“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

August 7, 2015 at 8:37 am

#1818133
RE: Tuning a Stored Procedure

Sean Lange (8/7/2015)
j-1064772 (8/7/2015)
[font="Comic Sans MS"]One simple improvement would be to avoid DDL in the middle of DML statements.
Move to the top, before any DML the following:[/font]
[font="Courier New"]CREATE TABLE...
^{“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

August 7, 2015 at 7:50 am

#1818101
RE: Extra join is killing query

serg-52 (8/7/2015)
I've checked the first query text using query diagrammer, namely off-line version of https://snowflakejoins.com/ and i see that
1) Product p is never used but in this...
^{“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

August 7, 2015 at 6:51 am

#1818073
RE: table with 3 million plus records taking half a minute, can I improve performance

HildaJ (8/6/2015)
Thank you in advance for any help.
I have a table that I need to do some computations on all the data but first I need to remove the duplicate...
^{“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

August 7, 2015 at 3:57 am

#1818039
RE: Extra join is killing query

ChrisM@Work (8/6/2015)
DavidDroog (8/5/2015)
Hi ChrisM
Replies below
1.Clients with “disciplines” filtered by geography using the geography parameter. This query should reference client, client address and geography tables plus those required to identify "disciplines"....
^{“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

August 7, 2015 at 1:55 am

#1818029
RE: Coalesce Vs Isnull

arrjay (8/7/2015)
Hi all - why does this query return;
12345
1234567890
SELECT ISNULL ( CONVERT(varchar(5), NULL), CONVERT(varchar(10), '1234567890') )
, COALESCE( CONVERT(varchar(5), NULL), CONVERT(varchar(10), '1234567890') )
Thanks!
https://msdn.microsoft.com/en-us/library/ms190349.aspx
Data type determination...
^{“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

August 7, 2015 at 1:50 am

#1818028
RE: Tuning a Stored Procedure

Those three aggregate subqueries queries against the 'performance' table appear to be very expensive. Why not do them in one shot, which would offer you tuning possibilities for that component...
^{“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

August 6, 2015 at 6:42 am

#1817824
RE: Extra join is killing query

DavidDroog (8/5/2015)
Hi ChrisM
Replies below
1.Clients with “disciplines” filtered by geography using the geography parameter. This query should reference client, client address and geography tables plus those required to identify "disciplines". 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

August 6, 2015 at 3:23 am

#1817788

Viewing 15 posts - 2,296 through 2,310 (of 10,143 total)