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)