sql query efficient or NOT?

Question

Post reply

sql query efficient or NOT?

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
March 21, 2007 at 5:27 pm

#92336

ALL ,
Does anybody see any inefficiencies with this query below selecting from a view? We want to use query for checking duplicate transactions in our database.
SELECT TOP 1 * FROM TrxDetailCard WHERE (Merchant_Key='5') AND (IsNull(Result_CH, '999') = '0') AND (IsNull(Void_Flag_CH, '0') <> '1') AND (Acct_Num_CH='rhYIopT75d7Gj0PjXvKe9ZAFOMiQVAJs') AND (Exp_CH='1208') AND (Total_Amt_MN=1.00) AND (Trans_Type_ID = 'Sale') AND (Date_DT >= cast( convert( char(10), getdate(), 121 ) as datetime )) ORDER BY TRX_HD_Key DESC
Thanks,
Dave

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

A few questions…

1. What field(s) constitutes a “duplicate”? You may also want to look up examples of IF EXISTS.

a. If it’s a single field, then only include that field in the result set.

i. SELECT yourField FROM TrxDetailCard WHERE ..

ii. IF EXISTS (SELECT 1 FROM TrxDetailCard WHERE .. )

2. Is the “Merchant_Key” field an integer/numeric? Comparing to '5' forces the server to convert.

a. If it is in fact numeric, then use this…

Merchant_Key=5

3. It’s unclear what you are trying to accomplish here …. “(IsNull(Result_CH, '999') = '0')” . Are you trying to eliminate Nulls only or Nulls and 0’s or only include 0’s?

a. To eliminate nulls ONLY

i. “Result_CH IS NOT NULL”

b. To eliminate NULLS OR 0’s

i. “(IsNull(Result_CH, 0) = 0)”

c. To only return 0’s, as NULLS never qual anything.

i. Result_CH = 0

You actually might get some better advice if you post more of the method that you are using to check for duplicates…..

Just my humble opinion..

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 2

Jason,

Thanks for your speedy response. I'll try to address your questions.

1. My understanding is that the logic in this query should check duplicate transactions with the passed criteria since 12am of that day. What constitues a duplicate is the same credit card number, expiration date, and purchase amount. If there is a duplicate then that would create a "duplicate transaction or decline" back to the merchant. Previously it was checking for duplicate transactions for the last 24 hours and it was causing problems with some of our merchants.

2. The "merchant_key" is in fact an integer field.

3. This one I can't answer right now. This particular query was passed to me for review as it was intended as a .NET patch for a previous (duplicate checking in last 24 hours) inefficient query. I'll check into this tomorrow.

Thanks for your advice and I'll try to get more info tomorrow.

Dave

Lowell SSC Guru Points: 323518 More actions · Answer 3

wouldn't this query return all duplicates:

SELECT Acct_Num_CH from TrxDetailCard

WHERE (Date_DT >= cast( convert( char(10), getdate(), 121 ) as datetime ))

group by Acct_Num_CH

having count(Acct_Num_CH) >1

with that, i think you could simply do this to get the info you were after a bit more efficiently:

SELECT TOP 1 * FROM TrxDetailCard INNER JOIN

(SELECT Acct_Num_CH from TrxDetailCard

WHERE (Date_DT >= cast( convert( char(10), getdate(), 121 ) as datetime ))

group by Acct_Num_CH

having count(Acct_Num_CH) >1

) X ON TrxDetailCard .Acct_Num_CH = X.Acct_Num_CH

WHERE (Merchant_Key='5')

AND (IsNull(Result_CH, '999') = '0')

AND (IsNull(Void_Flag_CH, '0') <> '1')

AND (Acct_Num_CH='rhYIopT75d7Gj0PjXvKe9ZAFOMiQVAJs')

AND (Exp_CH='1208')

AND (Total_Amt_MN=1.00)

AND (Trans_Type_ID = 'Sale')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!