Select statement result set to large

Question

Select statement result set to large

jdbrown239

SSCarpal Tunnel

Points: 4393
More actions
January 15, 2014 at 11:01 am

#396112

Hi
I am not sure how to pose this question but here goes.
I have a select statement joining tables from two databases where the result set returns matches for every occurrence of the items in the select portion. I am not sure how to write the query to only match for a single occurrence of an id field in one of the tables (like vendid). Any suggestions would be appreciated, thanks.
Query:
Select distinct d.dlr_reference_num,d.dlr_key,a.app_dlr_fkey, CONVERT(VARCHAR(30), a.app_key)AS RapAPP, c.user1, c.vendid from NJACCOUNTING01.marlin_test.dbo.apdoc c inner join dbo.application a
on CONVERT(VARCHAR(30), a.app_key) = SUBSTRING(c.user1, 1,6) , dbo.dealer d
where c.DocType = 'VO' and c.Crtd_DateTime > '2012-01-01 00:00:00' and c.VendId like '01%'
Sample result set:
dlr_reference_numdlr_keyapp_dlr_fkeyRapAPPuser1vendid
559298.872818621NULL925697925697YouthServ 10174334000
559299.3218622NULL925697925697YouthServ 10174334000
559299.790718623NULL925697925697YouthServ 10174334000
559299.909518624NULL925697925697YouthServ 10174334000
559318.609956466NULL925697925697YouthServ 10174334000
559323.133752356NULL925697925697YouthServ 10174334000
559323.147218625NULL925697925697YouthServ 10174334000
559325.949418626NULL925697925697YouthServ 10174334000
559333.38154621NULL925697925697YouthServ 10174334000
559338.138118627NULL925697925697YouthServ 10174334000
559347.043652315NULL925697925697YouthServ 10174334000
559351.055918628NULL925697925697YouthServ 10174334000
559359.02255540NULL925697925697YouthServ 10174334000
559361.4418629NULL925697925697YouthServ 10174334000
559375.867318630NULL925697925697YouthServ 10174334000
559431.328818631NULL925697925697YouthServ 10174334000
559431.379418632NULL925697925697YouthServ 10174334000
559431.8118633NULL925697925697YouthServ 10174334000
559431.828118634NULL925697925697YouthServ 10174334000
559431.854418635NULL925697925697YouthServ 10174334000
559432.700718636NULL925697925697YouthServ 10174334000

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You have a cross join with dbo.dealer. Is that correct? I suggest you to change the syntax to have only ANSI-92 joins.

For better help, please post DDL and sample data for your tables (doesn't need to be real data, just something we can work with). For help on how to do this, read the article linked in my signature.

I guess a CROSS APPLY with a TOP 1 would do the trick, but I can't assure anything.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

For anybody else stumbling across here I formatted this query so it is more legible.

SELECT DISTINCT d.dlr_reference_num,

d.dlr_key,

a.app_dlr_fkey,

CONVERT(VARCHAR(30), a.app_key)AS RapAPP,

c.user1,

c.vendid

FROM NJACCOUNTING01.marlin_test.dbo.apdoc c

INNER JOIN dbo.application a ON CONVERT(VARCHAR(30), a.app_key) = Substring(c.user1, 1, 6),

dbo.dealer d

WHERE c.DocType = 'VO'

AND c.Crtd_DateTime > '2012-01-01 00:00:00'

AND c.VendId LIKE '01%'

One suggestion I would make is if at all possible I would consider normalizing your data. You have pieces of data concatenated with other pieces of data in a single value. This is a violation of 1NF. Aside from that it is a huge performance bottleneck. Notice you have to case a.app_key and then get a substring of c.user. That means the engine has to do a table scan of both tables. This renders any indexing on those columns useless.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/