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...
>
>

Query on Custom source Component

Hi

in the acquireconnection method Using the below statment I can get a connection Object

oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

from the connection object I can get the connectionstring from the object by calling

oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.

How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.

any help is much appriciated

thanks in advance.

Just a guess, but look up OleDbConnection in the framework documentation, and see this-

The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.

Saying that, the password property may just be write-only, they often are for security, but I cannot find documentation for the extended properties right now.

|||

so does that mean that its not possible to extract password from connection Object

|||

Hi,

we are in process of developing custom source component. At design time we are using OLEDB connection to get the metadata.

we want to use the connection information given at design time by user to initialize OracleConnection of System.Data.OracleClient.

Can anybody guide us how to acheive this?

Thanks,

Anil

|||

To be clear you must be using an ADO.Net OLE-DB connection, not the native OLE-DB connection otherwise you won't be able to make head nor tail of the acquired connection. With that in mind, why not just use an ADO.NET OracleClient directly and stop messing about with "converting" it?

|||

Hi Darren,

Thanks for your reply. but we cant go ahead by specifying the connection type as ADO.Net: OracleClient as we are using the UI for some other component as well.

Is there any other way to do the same?

Thanks,

Anil.

|||Why do you want OleDb connections one minute and Oracle the next. They are very similar, but yet still different so masking one with the other does not make sense. Having multiple connections is not a problem. What UI? If it is your own Ui creating the OleDb connection, then why can it not create Oracle as well?|||

I created a single connection, ConnectionManagerType - ADO.NET:System.Data.OleDb.OleDbConnection..., which used the "Microsoft OLE DB Provider for Oracle" underneath.

I then used the following Script Task code to display the connection string and it shows the password just fine.

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections(0)

Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)

System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)

Dts.TaskResult = Dts.Results.Success

End Sub

I cannot see the problem, password is there, is this not what you want?

|||

Hi Darren

Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)

System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)

If the User Selects the Check Box "Save Password" then the password is there in the connection string Otherwise no.

Is there any way that we can get the password if the user has not select the option to save the password.

|||

No.

The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.