query on view

Hi,
I have following tables, I need to query all transaction for accounts. So I
created a view "txn_account" to simple union both tables and if a table
doesn't have a field I made it NULL in the view:
accounts (account_id, address, lastName, firstName, email, zipcode...)
credit_account(account_id, transaction_id, cc_digits, transaction_type,
creditcard_num, expire_date, amount, date_time...
bank_account(account_id, transaction_id, bank_center, account_num, aba_num,
amount, date_time...
Now i have query to get all credit card transaction with particular digits:
SELECT A.account_id,A.lastName,A.firstName,A.middleName,
A.suffix,A.address,A.cityName,A.zipcode,A.state,A.telephone,A.email,
sum(amount), creditcard_num, T.expire_date, T.cc_digits,
account_num, T.aba_num
FROM accounts A INNER JOIN txn_account T ON A.ccount_id = T.ccount_id
WHERE A.email='email@.somedomain.com' AND cc_digits='1034'
AND (transaction_type <> 'A' OR transaction_type Is Null)
GROUP BY A.account_id,A.lastName,A.firstName,A.middleName,
A.suffix,A.address,A.cityName,A.zipcode,A.state,A.telephone,A.email,
T.creditcard_num, T.expire_date, T.cc_digits, T.account_num, T.aba_num
this is the plan:
Concatenation
|--Filter(WHERE:([credit_account].[cc_digits]='4041' AND
([credit_account].[transaction_type]<>'A' OR
[credit_account].[transaction_type]=NULL)))
| |--Bookmark Lookup(BOOKMARK:([Bmk1002]),
OBJECT:([transaction].[pduser].[credit_account]))
| |--Index
S(OBJECT:([transaction].[pduser].[credit_account].[IX_credit_account_1]),
SEEK:([credit_account].[account_id]=[accounts].[account_id]) ORDERED FORWARD)
|-_Filter(WHERE:(([bank_account].[account_id]=[accounts].[account_id]
AND NULL='4041') AND (NULL<>'A' OR NULL=NULL))) //11%
| |--Clustered Index
Scan(OBJECT:([transaction].[pduser].[bank_account].[IX_bank_account]))
//87%, row count almost as whole table rows
The above plan showed that 98% time spent on bank_account table, which has
nothing to do with what want. Is it because the view? How can I make it
better?
ThanksCan you add a view that doesn't have the bank_account table referenced in
it?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:B01A3B2D-53EE-42C7-A7DD-25A970968FF4@.microsoft.com...
> Hi,
> I have following tables, I need to query all transaction for accounts. So
> I
> created a view "txn_account" to simple union both tables and if a table
> doesn't have a field I made it NULL in the view:
> accounts (account_id, address, lastName, firstName, email, zipcode...)
> credit_account(account_id, transaction_id, cc_digits, transaction_type,
> creditcard_num, expire_date, amount, date_time...
> bank_account(account_id, transaction_id, bank_center, account_num,
> aba_num,
> amount, date_time...
> Now i have query to get all credit card transaction with particular
> digits:
> SELECT A.account_id,A.lastName,A.firstName,A.middleName,
> A.suffix,A.address,A.cityName,A.zipcode,A.state,A.telephone,A.email,
> sum(amount), creditcard_num, T.expire_date, T.cc_digits,
> account_num, T.aba_num
> FROM accounts A INNER JOIN txn_account T ON A.ccount_id = T.ccount_id
> WHERE A.email='email@.somedomain.com' AND cc_digits='1034'
> AND (transaction_type <> 'A' OR transaction_type Is Null)
> GROUP BY A.account_id,A.lastName,A.firstName,A.middleName,
> A.suffix,A.address,A.cityName,A.zipcode,A.state,A.telephone,A.email,
> T.creditcard_num, T.expire_date, T.cc_digits, T.account_num, T.aba_num
> this is the plan:
> Concatenation
> |--Filter(WHERE:([credit_account].[cc_digits]='4041' AND
> ([credit_account].[transaction_type]<>'A' OR
> [credit_account].[transaction_type]=NULL)))
> | |--Bookmark Lookup(BOOKMARK:([Bmk1002]),
> OBJECT:([transaction].[pduser].[credit_account]))
> | |--Index
> S(OBJECT:([transaction].[pduser].[credit_account].[IX_credit_account_1]),
> SEEK:([credit_account].[account_id]=[accounts].[account_id]) ORDERED
> FORWARD)
> |-_Filter(WHERE:(([bank_account].[account_id]=[accounts].[account_id]
> AND NULL='4041') AND (NULL<>'A' OR NULL=NULL))) //11%
> | |--Clustered Index
> Scan(OBJECT:([transaction].[pduser].[bank_account].[IX_bank_account]))
> //87%, row count almost as whole table rows
> The above plan showed that 98% time spent on bank_account table, which has
> nothing to do with what want. Is it because the view? How can I make it
> better?
> Thanks
>|||the same query could have account_num as criteria then it will query
bank_account table.
"Louis Davidson" wrote:

> Can you add a view that doesn't have the bank_account table referenced in
> it?
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:B01A3B2D-53EE-42C7-A7DD-25A970968FF4@.microsoft.com...
>
>|||Well then you might need two views, or three, or even more. It really
depends on how much data you have and your performance needs. I would
suggest using stored procedures, then having your one query to handle all
cases. When you find a parameter config that is less than adequate and is
frequently used, optimize for it. In your case, have two queries, one with
and one withoout the bank table.
You may also find that you need to break these up into two subordinate
procedures:
create proc main
(
@.parm1
@.parm2
)
If @.parm1 is null --special case
exec sub1 @.parm2 = @.parm2
else
exec general @.parm1 = @.parm1, @.parm2 = @.parm2
go
create proc sub
(
@.parm2
) as
select blah..
where parm2 = @.parm2
etc.
It really depends on how well you can program each case. Try to keep each
proc down to only including SQL queries that will be executed to make it
easier to optimize.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4073AE7C-12C9-49DA-BF43-2A051DA8467C@.microsoft.com...
> the same query could have account_num as criteria then it will query
> bank_account table.
> "Louis Davidson" wrote:
>|||Thanks, sub procedures is good. I was struggling that the most search fields
are optional, so I had to dynamically compose the sql.
I was wondering why the sever doesn't use account_id index on bank_account
table to do the join as it used for the creditcard_account table, and used
the clustered index instead which basically scan the whole table since there
is no such account_id in bank_account table.
"Louis Davidson" wrote:

> Well then you might need two views, or three, or even more. It really
> depends on how much data you have and your performance needs. I would
> suggest using stored procedures, then having your one query to handle all
> cases. When you find a parameter config that is less than adequate and is
> frequently used, optimize for it. In your case, have two queries, one wit
h
> and one withoout the bank table.
> You may also find that you need to break these up into two subordinate
> procedures:
> create proc main
> (
> @.parm1
> @.parm2
> )
> If @.parm1 is null --special case
> exec sub1 @.parm2 = @.parm2
> else
> exec general @.parm1 = @.parm1, @.parm2 = @.parm2
> go
> create proc sub
> (
> @.parm2
> ) as
> select blah..
> where parm2 = @.parm2
> etc.
> It really depends on how well you can program each case. Try to keep each
> proc down to only including SQL queries that will be executed to make it
> easier to optimize.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:4073AE7C-12C9-49DA-BF43-2A051DA8467C@.microsoft.com...
>
>