Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts

Monday, March 26, 2012

How to use TOP in DELETE

Hi,

I have a large database in MSSQL 2005 (rows around 3,788,299 : size 4GB)

Two questions

1. SQL query ~ select top 100 * from [tablename], how do capture the next 100 record?

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

thk.

Hi,

1. not sure about this one...
2. delete top(100) [tablename] where ........

James Steele

|||

One approach for each question:

Q1:

In SQL Server 2005, we can use ROW_NUMBER() function

SELECT ROW_NUMBER() OVER(ORDER BY id ) AS Row_Number, * FROM yourTable WHERE Row_Number<=100 -- first 100

-----

WHERE Row_Number>100 and Row_Number<=200 --100-200

Q2:

DELETE

tbl_test1WHERE IDIN(

SELECT

TOP 100 IDFROM tbl_test1ORDERBY id)|||

To select the next 100 records, try using ROW_NUMBER(). You can get details from Books Online, or the article below shows how to use it for custom paging.

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

|||

2. SQL query ~ delete top 100 from [tablename] -- MSSQL show me syntax error? How can I only delete the top 100 record?

New in SQL Server 2005, you can use TOP along with DELETE.

DELETE TOP (100) FROM yourTable

works!

But in SQL Server 2000, this one will not work.

In SQL Server 2000, use this instead:

DELETE yourTable FROM (SELECT TOP 10 * FROM yourTable) AS t1 WHERE yourTable.id=t1.id

How to use the Top N in Filters

Hi,
I have created one report which uses the stored proc. it returns the data
more than 100 rows but i want to show only top 20 records in the graph( I
have to use that stored proc only. not possible to create another stored
proc)..
How can i achieve that? I found one property at filters sections i.e Top
N. Could any one let me know the usage of TOP N.
Regards,
SriHope you have used table control, just right click for properties. select the
filter tab In the expressions select the first column of your dataset. Select
the "Top N" from the operator. now in the value just type =20. You get first
20 rows.
Amarnath
"Sriman" wrote:
> Hi,
> I have created one report which uses the stored proc. it returns the data
> more than 100 rows but i want to show only top 20 records in the graph( I
> have to use that stored proc only. not possible to create another stored
> proc)..
> How can i achieve that? I found one property at filters sections i.e Top
> N. Could any one let me know the usage of TOP N.
> Regards,
> Sri

Monday, March 19, 2012

How to Use RownNumber in a Matrix?

Hello Guys,

I am trying to count the number of rows that are displayed on my matrix report and display it in a textbox. What I use for the table reports doesn't really work:

="Number of records displayed: " + cstr(RowNumber("Data_Set_Name"))

This will return a number of rows returned by this data set. However, the count of rows that are actually being displayed in the matrix is very different. If anyone dealt with this before please let me know how to solve it. Thank you very much!

Try =CountRows(Fields!FieldName.Value) or =CountRows("DataSetName").

|||

I actually brought in another field, and did CountDistinct on it - now it works like a charm. Thank you so much for pointing me in the right direction.

How to use PIVOT for dynamic transform rows to columns

Hi,
I want to use PIVOT to transfer my rows data to columns(for example in
10 rows one columns is price which has prices of each month). The
result is showing 10 rows and column containing price.
I want to see price of 10 rows(for each month) in column manner means
for result should display row along with all prices in each column one
after another.
The PIVOT can be used very well but in my case the result rows are not
fixed so that result no of columns also.
Please suggest me that how can I transform dynamic number of rows to
columns.
if anyother method will solve the problem please suggest.
Thanks,
Anurag
Currently the PIVOT operator does not support dynamic pivoting. Here are a
few resources on how it can be accomplished:
http://www.sqlmag.com/Articles/ArticleID/93907/93907.html
http://www.sqlmag.com/articles/index.cfm?articleid=94268
http://www.sqlmag.com/articles/index.cfm?articleid=15608
http://www.sqlteam.com/item.asp?ItemID=2955
Also, most reporting tools have very good support for pivoting/cross tab
functionality. That might be a better options based on your
situation/requirements.
If you feel this is a much needed functionality in SQL Server, you can vote
to have it implemented here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127071
HTH,
Plamen Ratchev
http://www.SQLStudio.com

How to use PIVOT for dynamic transform rows to columns

Hi,
I want to use PIVOT to transfer my rows data to columns(for example in
10 rows one columns is price which has prices of each month). The
result is showing 10 rows and column containing price.
I want to see price of 10 rows(for each month) in column manner means
for result should display row along with all prices in each column one
after another.
The PIVOT can be used very well but in my case the result rows are not
fixed so that result no of columns also.
Please suggest me that how can I transform dynamic number of rows to
columns.
if anyother method will solve the problem please suggest.
Thanks,
AnuragCurrently the PIVOT operator does not support dynamic pivoting. Here are a
few resources on how it can be accomplished:
http://www.sqlmag.com/Articles/Arti...3907/93907.html
http://www.sqlmag.com/articles/inde...articleid=94268
http://www.sqlmag.com/articles/inde...articleid=15608
http://www.sqlteam.com/item.asp?ItemID=2955
Also, most reporting tools have very good support for pivoting/cross tab
functionality. That might be a better options based on your
situation/requirements.
If you feel this is a much needed functionality in SQL Server, you can vote
to have it implemented here:
http://connect.microsoft.com/SQLSer...=12707
1
HTH,
Plamen Ratchev
http://www.SQLStudio.com

How to use PIVOT for dynamic transform rows to columns

Hi,
I want to use PIVOT to transfer my rows data to columns(for example in
10 rows one columns is price which has prices of each month). The
result is showing 10 rows and column containing price.
I want to see price of 10 rows(for each month) in column manner means
for result should display row along with all prices in each column one
after another.
The PIVOT can be used very well but in my case the result rows are not
fixed so that result no of columns also.
Please suggest me that how can I transform dynamic number of rows to
columns.
if anyother method will solve the problem please suggest.
Thanks,
AnuragCurrently the PIVOT operator does not support dynamic pivoting. Here are a
few resources on how it can be accomplished:
http://www.sqlmag.com/Articles/ArticleID/93907/93907.html
http://www.sqlmag.com/articles/index.cfm?articleid=94268
http://www.sqlmag.com/articles/index.cfm?articleid=15608
http://www.sqlteam.com/item.asp?ItemID=2955
Also, most reporting tools have very good support for pivoting/cross tab
functionality. That might be a better options based on your
situation/requirements.
If you feel this is a much needed functionality in SQL Server, you can vote
to have it implemented here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127071
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Monday, March 12, 2012

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

how to use multiple delimiters for the same flat file

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

Quote:

Originally Posted by gopiganguly

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.


How do you know there are 820 lines?

If you saw it in a text editor, it sounds like a different linefeed is being used (carriage return + linefeed combination opposed to just linefeed).

Otherwise, replace nulls with linefeed characters. You can use the SQL REPLACE function. Or, open the file with filesystemobject.textstream, readall(), then do a replace on the returned stream.

Wednesday, March 7, 2012

How to use comma separated value list in the where clause?

How to use comma separated value list in the where clause?

I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list).

update tbl_Voters

set voted = true

whereEmpID in @.empIdsCsv

Where, @.empIdsCsv = ’12,23,345,’ (IDs of the employees)

Since the above is not possible I have done the following dynamic query:

-- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}…

set @.empIdsCsv = 'EmpID=' + substring(@.empIdsCsv , 0, len(@.empIdsCsv ))-- Remove trailing comma

set @.empIdsCsv = replace(@.empIdsCsv , ',', ' or EmpID=')

declare @.markVoters varchar(8000)

set @.markVoters = '

update tbl_Voters

setvoted = true

where ’ + @.empIdsCsv

--Execute the dinamic query

exec (@.markVoters)

The above code generates the following dynamic query:

update tbl_Voters

setvoted = true

where

EmpID= 12 or EmpID=23 or EmpID=345

The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars).

Can someone suggest a better solution with the ability to use comma seperated values in the where clause?

there is the IN clause however I somehow get the feeling that will not help performance that much. The syntax is
select columns from tablename where EMplID IN (12, 23, 356, ....)

Hope this helps
|||

Please take a look at the link below that describes various techniques on how to pass a list of values from client to SQL Server.

http://www.sommarskog.se/arrays-in-sql.html

Sunday, February 19, 2012

How to use a composite PK in transact SQL ?

Heres the problem...
I have a huge table (about 30 columns, 22 million rows). I am doing a simple
select of 2 fields. One of these fields is a date field. It is indexed.
Running a query on this takes about 25 seconds. If I add another field (a
non-indexed amount field) the same select takes more than 12 minutes. (In
the code below the offending field is commented out)
DECLARE @.as_of_dt DATETIME, @.reporting_year SMALLINT
SET @.as_of_dt = CONVERT(DATETIME, '20040630', 112)
SET @.reporting_year = DATEPART(YEAR, @.as_of_dt)
SELECT
ft.tran_trade_dt,
tt.trant_dsc
/* ft.tran_tot_ordr_amt*/
FROM
ftran_header_t ft
JOIN rpstrant_t tt ON tt.trant_cd = ft.trant_cd
WHERE
UPPER(tt.trant_cd) LIKE 'BCM%'
So obviously then I have an issue with this non-indexed amount field. My DBA
said to make sure I am using the Primary Key. Great. The primary key is a
composite of 4 different fields...
pk_ftran_header_t - clustered, unique, primary key located on PRIMARY -
acct_nbr, ser_id, serp_seq_nbr, tran_nbr
None of these fields are used in my query. I don't know how I would ensure
that I would use the primary key in my query, I don't understand what Syntax
would be required.
Any help appreciated....
Thanks."quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:%23AX8nmNIFHA.2740@.TK2MSFTNGP12.phx.gbl...
> UPPER(tt.trant_cd) LIKE 'BCM%'
Here's one problem. Are you actually using a case-sensitive collation?
If not, remove the UPPER() -- it's not giving you any benefit and is
destroying sargability of this predicate.
As for using the primary key -- are those columns in the table you're
joining to? Perhaps you should ask your DBA for some clarification on how
to properly do the join.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Hi,
Thanks for your help. Changed the UPPER statement, it didn't make a huge
difference as the table it refers to is a lookup table (I inherited this
code by the way).
Checked with the DBA about use of primary key, but I won't be able to get
his time for a couple of days and was hoping to satisfy my own curiosity
till then.
By the way, the columns in the primary key are not in the table I am joining
to.
Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eoSGmsNIFHA.2704@.tk2msftngp13.phx.gbl...
> "quiglepops" <dave_quigley@.hotmail.com> wrote in message
> news:%23AX8nmNIFHA.2740@.TK2MSFTNGP12.phx.gbl...
> Here's one problem. Are you actually using a case-sensitive
collation?
> If not, remove the UPPER() -- it's not giving you any benefit and is
> destroying sargability of this predicate.
> As for using the primary key -- are those columns in the table you're
> joining to? Perhaps you should ask your DBA for some clarification on how
> to properly do the join.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||On Fri, 4 Mar 2005 16:46:01 -0000, quiglepops wrote:
(snip)
>DECLARE @.as_of_dt DATETIME, @.reporting_year SMALLINT
>SET @.as_of_dt = CONVERT(DATETIME, '20040630', 112)
>SET @.reporting_year = DATEPART(YEAR, @.as_of_dt)
>SELECT
>ft.tran_trade_dt,
>tt.trant_dsc
>/* ft.tran_tot_ordr_amt*/
>FROM
>ftran_header_t ft
>JOIN rpstrant_t tt ON tt.trant_cd = ft.trant_cd
>WHERE
>UPPER(tt.trant_cd) LIKE 'BCM%'
Hi quiglepops,
Is this the complete query? I ask this because you declare and calculate
the variables @.as_of_dt and @.reporting_year, but they are not used in
the query.
Based on your description, the query with the third column commented out
uses a covering index (an nonclustered index on columns tran_trade_dt
and trant_cd, plus maybe other columns). SQL Server only has to scan the
index pages to get all data it needs. When you uncomment the third
column (that is obviously NOT in the index), you force SQL Server to
fetch the data page for each row that matches the WHERE clause. The
optimizer now has to choose: either use the index and fetch a data page
for each matching row (with the risk that the same data page is
retrieved multiple times), or just disregard the index and do a straight
table scan instead. Based on the absence of a sargable expression in the
WHERE, and the absence of ANY filter that opertes directly on the big
table, I wouldn't be surprised if the optimizer settles for the table
scan, as it has no way to guess the percentage of matching rows.
Try the version below (incorporating Adam's suggestion and also adding a
redundant extra expression to the WHERE clause to point out the obvious
to the optimizer) to see if that makes any difference:
SELECT ft.tran_trade_dt,
tt.trant_dsc,
ft.tran_tot_ordr_amt
FROM ftran_header_t AS ft
JOIN rpstrant_t AS tt
ON tt.trant_cd = ft.trant_cd
WHERE tt.trant_cd LIKE 'BCM%'
AND ft.trant_cd LIKE 'BCM%'
(If this works, you could also try what happens if you remove the
original LIKE and keep only the one on the ftran_header table).
If the above suggestion doesn't help, you should talk to your DBA and
ask if (s)he is willing to either add tran_tot_ordr_amt to the covering
index used by the quick version of your query, or to add an extra index
specifically for this query to the database. The DBA will have to weigh
the benefits (for your query) versus the drawbacks (expanding the index
used now might hurt other queries; adding an extra index will hurt the
speed of data modification).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo. You are a genius. Execution time is down to 35 seconds with that extra
line added in.
Thanks so much guys. Now I just have to reread and understand why this is
!!!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:mf2n21psmuraohpj5i3vctkt0svkblch8i@.
4ax.com...
> On Fri, 4 Mar 2005 16:46:01 -0000, quiglepops wrote:
> (snip)
> Hi quiglepops,
> Is this the complete query? I ask this because you declare and calculate
> the variables @.as_of_dt and @.reporting_year, but they are not used in
> the query.
> Based on your description, the query with the third column commented out
> uses a covering index (an nonclustered index on columns tran_trade_dt
> and trant_cd, plus maybe other columns). SQL Server only has to scan the
> index pages to get all data it needs. When you uncomment the third
> column (that is obviously NOT in the index), you force SQL Server to
> fetch the data page for each row that matches the WHERE clause. The
> optimizer now has to choose: either use the index and fetch a data page
> for each matching row (with the risk that the same data page is
> retrieved multiple times), or just disregard the index and do a straight
> table scan instead. Based on the absence of a sargable expression in the
> WHERE, and the absence of ANY filter that opertes directly on the big
> table, I wouldn't be surprised if the optimizer settles for the table
> scan, as it has no way to guess the percentage of matching rows.
> Try the version below (incorporating Adam's suggestion and also adding a
> redundant extra expression to the WHERE clause to point out the obvious
> to the optimizer) to see if that makes any difference:
> SELECT ft.tran_trade_dt,
> tt.trant_dsc,
> ft.tran_tot_ordr_amt
> FROM ftran_header_t AS ft
> JOIN rpstrant_t AS tt
> ON tt.trant_cd = ft.trant_cd
> WHERE tt.trant_cd LIKE 'BCM%'
> AND ft.trant_cd LIKE 'BCM%'
> (If this works, you could also try what happens if you remove the
> original LIKE and keep only the one on the ftran_header table).
> If the above suggestion doesn't help, you should talk to your DBA and
> ask if (s)he is willing to either add tran_tot_ordr_amt to the covering
> index used by the quick version of your query, or to add an extra index
> specifically for this query to the database. The DBA will have to weigh
> the benefits (for your query) versus the drawbacks (expanding the index
> used now might hurt other queries; adding an extra index will hurt the
> speed of data modification).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)