Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have these two Microsoft SQL Server 2005 tables:

ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber)
1|1|A|000001
2|1|A|000002
3|1|B|000003
4|2|C|000004

Objects (ObjectID [PK])
1
2
3

Table ObjectLicenses has a primary key on column ObjectLicenseID. Table Objects has a primary key on column ObjectID.

I want to return a list of all LicenseType's, by Object ID. However, this takes a long time, possibly due to lack of index(es)...currently, I use a "FOR XML PATH" query to do this:

SELECT      O.ObjectID

            ,STUFF( (SELECT      '; ' + 
                                 CONVERT(VARCHAR(12), ROW_NUMBER() OVER 
                                 (ORDER BY OL.ObjectLicenseID)) + 
                                 ') ' + ISNULL(OL.LicenseType, '')

                       FROM      ObjectLicenses OL

                      WHERE      O.ObjectID = OL.ObjectID
                      FOR XML PATH(''), TYPE
                    ).value('.', 'VARCHAR(MAX)')
                    , 1, 2, ''
                  ) AS 'LicenseType'

  FROM      Objects O

How might I index or otherwise change this model/query to speed it up?

share|improve this question
2  
Please don't convert to varchar without specifying a length (in this case, 12 is probably fine). – Aaron Bertrand Jun 20 '12 at 17:27
1  
that wasn't the point. Did you read the link in my comment? – Aaron Bertrand Jun 28 '12 at 18:21
2  
You're still missing my point. If you need varchar(max), or varchar(1024), or whatever, that's not the point. Don't just say varchar, say varchar(whatever the max need is)... hat way you don't get surprised because in one case you'll get a varchar(30) and in others you'll get a varchar(1). Note that my comment wasn't meant as a fix for your performance issue, just a fix for the bad habit of not bothering to specify the length of your varchar variables/conversions. – Aaron Bertrand Jun 28 '12 at 18:37
1  
In this case, also, the TYPE).value... stuff is probably unnecessary. That's primarily used to prevent problems with XML entitization - it doesn't look like your sample data could contain &, >, < etc. – Aaron Bertrand Jun 28 '12 at 18:48
1  
Is there any reason you need both tables involved in the query? Seems to me you could avoid some operations by just querying against ObjectLicenses (unless you also want to expose cases where an Object has no licenses assigned). Also, what indexes are on the ObjectLicenses table (and no, the foreign key doesn't count). – Aaron Bertrand Jun 28 '12 at 20:04
show 7 more comments

1 Answer

up vote 3 down vote

The relationship seems fine; the problem seems to be in the presentation. If you want this query to work faster, just return the join to the client, and perform the looping / concatenating on the client.

This sounds like a tongue-in-cheek answer, but I'm 100% serious. Why does SQL Server have to do the work of transposing your rows to a concatenated column? Your client language is certainly much better equipped to do that (loop through the joined results, ordered by ObjectID, and start a new concat whenever a new ObjectID is encountered). It may be less efficient at the very high end (say, thousands of license types per ObjectID) but I suspect you're not at an edge case.

share|improve this answer
Thanks for thinking out-of-the-box. We currently feed the SQL Server results directly to end users via Microsoft Access ODBC links, for simplicity. So, no query post-processing occurs. Long-term, we plan to migrate this data to a ASP.NET web app, which would allow this. – schultkl Jun 28 '12 at 18:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.