|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How many sub-selects and better options?I am working on an ecommerce website, and I recently implemented paging of product items from a chosen category. The first approach was to keep the SQL simple, so I link all products to a single category. However my client is asking that it be possible to browse all items that exist within a category, rather than having to click the final leaf category, E.g. Category: Mind, Body, Spirit Sub-Cat: Spiritualism Sub-Cat: Meditation Techniques Now, if the user clicks Mind, Body, Spirit I need to show ALL products that fall underneath, including everything from Spiritualism and Meditation Techniques. This SQL is a snippet of the main query to return a set of results based on paging - the paging code has been omitted, and the inner select deals with choosing the correct set of results to support paging: Select P.Name Publisher, Z.ProductID, Z.Name, Z.Authors, Z.Price, Z.DateOfPub, LEFT(Z.Description, 500) Description, Z.SaleIndex, Z.ImageLink, Z.PreviewImageLink From Publishers P INNER JOIN ( Products Z Inner Join ( Select top 5 A.ProductID from ( Select top 5 B1.ProductID, B1.Name from Products B1, categoryProducts B2 where B2.CategoryID = 1 and B1.ProductID = B2.ProductID Order By B1.Name ASC ) A order by A.Name DESC) Y On Z.ProductID = Y.ProductID ) ON P.PublisherID = Z.PublisherID Order by Z.Name ASC First question - there are a number of inner joins going on - is there a better way to do this? In the code above the CategoryID = 1, so if Mind, Body, Spirit had an ID of 1, then only books with a category of 1 would be chosen, and books underneath this category would not be chosen. After some research I have worked out how to set my catgories up to allow a hierarchical representation. Assuming I have the following table structures: Products ======== ProductID Name Categories ========== CategoryID Name Linkage CategoryProducts ================ CategoryID ProductID And the linkage column for the categories mentioned are: Mind, Body, Spirit = 1.1 Sub-Cat: Spiritualism = 1.1.1 Sub-Cat: Meditation Techniques = 1.1.2 Then I can find out all books underneath Mind Body and Spirt using: SELECT P.* FROM products P INNER JOIN ( SELECT ProductID FROM categoryproducts CP WHERE CP.CategoryID in ( SELECT categoryID FROM Categories WHERE Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4) ) ) A ON A.ProductID = p.ProductID However, to support paging, I need to link this query with the previous query. I feel there might be too many selects going on when I do this (but I am no expert), and secondly, I havent got a clue how to incorporate this query above with the original higher up! Help! jr. For paging solutions:
http://www.aspfaq.com/show.asp?id=2120 For browsing your categories, joins are good if the hierarchy is of a known, fixed depth. For trees of arbitrary depth you should consider a hierarchy model other than a simple adjacency list. Google for "trees", "nested sets" and "materialized path" in the archives of this group, this is an FAQ. -- David Portas SQL Server MVP -- Might want to get a copy of TREES & HIERARCHIES IN SQL for the database
side. I don't do paging -- much too front end :) Thanks David and Celko. I have introduced the materialized path concept,
which is shown in the original post - I named the path field Linkage, which I admit isnt a very good name. jr. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1120937699.723212.112000@g14g2000cwa.googlegroups.com... > Might want to get a copy of TREES & HIERARCHIES IN SQL for the database > side. I don't do paging -- much too front end :) > Looking at this, there is only a finite number of levels (i.e. 3) A quick
answer for is to make use of an indexed view. They work with all versions of SQL Server (except the Desktop Engine I think). However for all versions except enterprise you need to add the NoExpand table hint in order to get SQL to use the index ;-) OK, the basic idea if that you create a massive table which performs the joins, not at select time, but at insert time. However, as it's only ever one row being altered at a time, the amount of overhead to updating the indexed view is alot lower than performing the join every time that you do the select. Also as it's only one row, it won't take long to update. Show quote "J Rieggle" <jrieg***@hotmail.co.uk> wrote in message news:OWb7WvJhFHA.3936@tk2msftngp13.phx.gbl... > Hi. > > I am working on an ecommerce website, and I recently implemented paging of > product items from a chosen category. The first approach was to keep the > SQL simple, so I link all products to a single category. However my > client is asking that it be possible to browse all items that exist within > a category, rather than having to click the final leaf category, E.g. > > Category: Mind, Body, Spirit > Sub-Cat: Spiritualism > Sub-Cat: Meditation Techniques > > Now, if the user clicks Mind, Body, Spirit I need to show ALL products > that fall underneath, including everything from Spiritualism and > Meditation Techniques. This SQL is a snippet of the main query to return > a set of results based on paging - the paging code has been omitted, and > the inner select deals with choosing the correct set of results to support > paging: > > Select P.Name Publisher, Z.ProductID, Z.Name, Z.Authors, Z.Price, > Z.DateOfPub, > LEFT(Z.Description, 500) Description, Z.SaleIndex, Z.ImageLink, > Z.PreviewImageLink > From > Publishers P INNER JOIN ( > Products Z Inner Join ( > Select top 5 A.ProductID > from ( > Select top 5 B1.ProductID, B1.Name > from > Products B1, categoryProducts B2 > where > B2.CategoryID = 1 and B1.ProductID = B2.ProductID > Order By B1.Name ASC > ) A > order by A.Name DESC) > Y On Z.ProductID = Y.ProductID > ) ON P.PublisherID = Z.PublisherID > Order by Z.Name ASC > > First question - there are a number of inner joins going on - is there a > better way to do this? > > In the code above the CategoryID = 1, so if Mind, Body, Spirit had an ID > of 1, then only books with a category of 1 would be chosen, and books > underneath this category would not be chosen. After some research I have > worked out how to set my catgories up to allow a hierarchical > representation. Assuming I have the following table structures: > > Products > ======== > ProductID > Name > > Categories > ========== > CategoryID > Name > Linkage > > CategoryProducts > ================ > CategoryID > ProductID > > And the linkage column for the categories mentioned are: > > Mind, Body, Spirit = 1.1 > Sub-Cat: Spiritualism = 1.1.1 > Sub-Cat: Meditation Techniques = 1.1.2 > > Then I can find out all books underneath Mind Body and Spirt using: > > SELECT P.* > FROM > products P INNER JOIN > ( > SELECT ProductID > FROM categoryproducts CP > WHERE CP.CategoryID in ( > SELECT categoryID > FROM > Categories > WHERE > Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4) > ) > ) A > ON A.ProductID = p.ProductID > > However, to support paging, I need to link this query with the previous > query. I feel there might be too many selects going on when I do this > (but I am no expert), and secondly, I havent got a clue how to incorporate > this query above with the original higher up! > > Help! > > jr. > > > Colin,
Thanks for this answer. Could you elaborate more on this, because I am not very good with SQL? Also, the example has 3 levels, but in reality it may well be more complicated and deeper - would this break the indexed view approach? jr. Show quote "Colin Dawson" <newsgro***@cjdawson.com> wrote in message news:45Wze.66340$G8.22679@text.news.blueyonder.co.uk... > Looking at this, there is only a finite number of levels (i.e. 3) A quick > answer for is to make use of an indexed view. > They work with all versions of SQL Server (except the Desktop Engine I > think). However for all versions except enterprise you need to add the > NoExpand table hint in order to get SQL to use the index ;-) > > OK, the basic idea if that you create a massive table which performs the > joins, not at select time, but at insert time. However, as it's only ever > one row being altered at a time, the amount of overhead to updating the > indexed view is alot lower than performing the join every time that you do > the select. Also as it's only one row, it won't take long to update. > > > "J Rieggle" <jrieg***@hotmail.co.uk> wrote in message > news:OWb7WvJhFHA.3936@tk2msftngp13.phx.gbl... >> Hi. >> >> I am working on an ecommerce website, and I recently implemented paging >> of product items from a chosen category. The first approach was to keep >> the SQL simple, so I link all products to a single category. However my >> client is asking that it be possible to browse all items that exist >> within a category, rather than having to click the final leaf category, >> E.g. >> >> Category: Mind, Body, Spirit >> Sub-Cat: Spiritualism >> Sub-Cat: Meditation Techniques >> >> Now, if the user clicks Mind, Body, Spirit I need to show ALL products >> that fall underneath, including everything from Spiritualism and >> Meditation Techniques. This SQL is a snippet of the main query to return >> a set of results based on paging - the paging code has been omitted, and >> the inner select deals with choosing the correct set of results to >> support paging: >> >> Select P.Name Publisher, Z.ProductID, Z.Name, Z.Authors, Z.Price, >> Z.DateOfPub, >> LEFT(Z.Description, 500) Description, Z.SaleIndex, Z.ImageLink, >> Z.PreviewImageLink >> From >> Publishers P INNER JOIN ( >> Products Z Inner Join ( >> Select top 5 A.ProductID >> from ( >> Select top 5 B1.ProductID, B1.Name >> from >> Products B1, categoryProducts B2 >> where >> B2.CategoryID = 1 and B1.ProductID = B2.ProductID >> Order By B1.Name ASC >> ) A >> order by A.Name DESC) >> Y On Z.ProductID = Y.ProductID >> ) ON P.PublisherID = Z.PublisherID >> Order by Z.Name ASC >> >> First question - there are a number of inner joins going on - is there a >> better way to do this? >> >> In the code above the CategoryID = 1, so if Mind, Body, Spirit had an ID >> of 1, then only books with a category of 1 would be chosen, and books >> underneath this category would not be chosen. After some research I have >> worked out how to set my catgories up to allow a hierarchical >> representation. Assuming I have the following table structures: >> >> Products >> ======== >> ProductID >> Name >> >> Categories >> ========== >> CategoryID >> Name >> Linkage >> >> CategoryProducts >> ================ >> CategoryID >> ProductID >> >> And the linkage column for the categories mentioned are: >> >> Mind, Body, Spirit = 1.1 >> Sub-Cat: Spiritualism = 1.1.1 >> Sub-Cat: Meditation Techniques = 1.1.2 >> >> Then I can find out all books underneath Mind Body and Spirt using: >> >> SELECT P.* >> FROM >> products P INNER JOIN >> ( >> SELECT ProductID >> FROM categoryproducts CP >> WHERE CP.CategoryID in ( >> SELECT categoryID >> FROM >> Categories >> WHERE >> Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4) >> ) >> ) A >> ON A.ProductID = p.ProductID >> >> However, to support paging, I need to link this query with the previous >> query. I feel there might be too many selects going on when I do this >> (but I am no expert), and secondly, I havent got a clue how to >> incorporate this query above with the original higher up! >> >> Help! >> >> jr. >> >> >> > > I'm not sure about how to implement using the example SQL query that you
provided. The reason is that you're using Select Top and Order By's in the inline views. I know that you are not allowed to perform any aggregate type functions i.e. group by's, counts and stuff like that. I'd spend some time, maybe a couple of days re-working that Select statement to see if it can be flattened first. I'd also break it down into it's component parts, so that I could fully understand it. I'd then once the Query is simplified as much as possible.... that includes making sure that all joins are using the same syntax, not using an inline view if it's really not needed, etc. It would also be worthwhile playing about with indexed views so that you get to see how they work, even if they're not useful this exact scenario, there will always be another time that they'll get you out of a jam. Show quote "J Rieggle" <jrieg***@hotmail.co.uk> wrote in message news:eNdQByShFHA.3300@TK2MSFTNGP15.phx.gbl... > Colin, > > Thanks for this answer. Could you elaborate more on this, because I am > not very good with SQL? Also, the example has 3 levels, but in reality it > may well be more complicated and deeper - would this break the indexed > view approach? > > jr. > > "Colin Dawson" <newsgro***@cjdawson.com> wrote in message > news:45Wze.66340$G8.22679@text.news.blueyonder.co.uk... >> Looking at this, there is only a finite number of levels (i.e. 3) A >> quick answer for is to make use of an indexed view. >> They work with all versions of SQL Server (except the Desktop Engine I >> think). However for all versions except enterprise you need to add the >> NoExpand table hint in order to get SQL to use the index ;-) >> >> OK, the basic idea if that you create a massive table which performs the >> joins, not at select time, but at insert time. However, as it's only >> ever one row being altered at a time, the amount of overhead to updating >> the indexed view is alot lower than performing the join every time that >> you do the select. Also as it's only one row, it won't take long to >> update. >> >> >> "J Rieggle" <jrieg***@hotmail.co.uk> wrote in message >> news:OWb7WvJhFHA.3936@tk2msftngp13.phx.gbl... >>> Hi. >>> >>> I am working on an ecommerce website, and I recently implemented paging >>> of product items from a chosen category. The first approach was to keep >>> the SQL simple, so I link all products to a single category. However my >>> client is asking that it be possible to browse all items that exist >>> within a category, rather than having to click the final leaf category, >>> E.g. >>> >>> Category: Mind, Body, Spirit >>> Sub-Cat: Spiritualism >>> Sub-Cat: Meditation Techniques >>> >>> Now, if the user clicks Mind, Body, Spirit I need to show ALL products >>> that fall underneath, including everything from Spiritualism and >>> Meditation Techniques. This SQL is a snippet of the main query to >>> return a set of results based on paging - the paging code has been >>> omitted, and the inner select deals with choosing the correct set of >>> results to support paging: >>> >>> Select P.Name Publisher, Z.ProductID, Z.Name, Z.Authors, Z.Price, >>> Z.DateOfPub, >>> LEFT(Z.Description, 500) Description, Z.SaleIndex, Z.ImageLink, >>> Z.PreviewImageLink >>> From >>> Publishers P INNER JOIN ( >>> Products Z Inner Join ( >>> Select top 5 A.ProductID >>> from ( >>> Select top 5 B1.ProductID, B1.Name >>> from >>> Products B1, categoryProducts B2 >>> where >>> B2.CategoryID = 1 and B1.ProductID = B2.ProductID >>> Order By B1.Name ASC >>> ) A >>> order by A.Name DESC) >>> Y On Z.ProductID = Y.ProductID >>> ) ON P.PublisherID = Z.PublisherID >>> Order by Z.Name ASC >>> >>> First question - there are a number of inner joins going on - is there a >>> better way to do this? >>> >>> In the code above the CategoryID = 1, so if Mind, Body, Spirit had an ID >>> of 1, then only books with a category of 1 would be chosen, and books >>> underneath this category would not be chosen. After some research I >>> have worked out how to set my catgories up to allow a hierarchical >>> representation. Assuming I have the following table structures: >>> >>> Products >>> ======== >>> ProductID >>> Name >>> >>> Categories >>> ========== >>> CategoryID >>> Name >>> Linkage >>> >>> CategoryProducts >>> ================ >>> CategoryID >>> ProductID >>> >>> And the linkage column for the categories mentioned are: >>> >>> Mind, Body, Spirit = 1.1 >>> Sub-Cat: Spiritualism = 1.1.1 >>> Sub-Cat: Meditation Techniques = 1.1.2 >>> >>> Then I can find out all books underneath Mind Body and Spirt using: >>> >>> SELECT P.* >>> FROM >>> products P INNER JOIN >>> ( >>> SELECT ProductID >>> FROM categoryproducts CP >>> WHERE CP.CategoryID in ( >>> SELECT categoryID >>> FROM >>> Categories >>> WHERE >>> Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE >>> CategoryID=4) >>> ) >>> ) A >>> ON A.ProductID = p.ProductID >>> >>> However, to support paging, I need to link this query with the previous >>> query. I feel there might be too many selects going on when I do this >>> (but I am no expert), and secondly, I havent got a clue how to >>> incorporate this query above with the original higher up! >>> >>> Help! >>> >>> jr. >>> >>> >>> >> >> > > Colin,
Thanks for your helpful mail. I will spend soetime looking at indexed views, because I admit I know nothing of them. Thanks jr. Show quote "Colin Dawson" <newsgro***@cjdawson.com> wrote in message news:PefAe.66986$G8.31364@text.news.blueyonder.co.uk... > I'm not sure about how to implement using the example SQL query that you > provided. The reason is that you're using Select Top and Order By's in > the inline views. > > I know that you are not allowed to perform any aggregate type functions > i.e. group by's, counts and stuff like that. I'd spend some time, maybe a > couple of days re-working that Select statement to see if it can be > flattened first. I'd also break it down into it's component parts, so > that I could fully understand it. I'd then once the Query is simplified > as much as possible.... that includes making sure that all joins are > using the same syntax, not using an inline view if it's really not needed, > etc. > > It would also be worthwhile playing about with indexed views so that you > get to see how they work, even if they're not useful this exact scenario, > there will always be another time that they'll get you out of a jam. > > "J Rieggle" <jrieg***@hotmail.co.uk> wrote in message > news:eNdQByShFHA.3300@TK2MSFTNGP15.phx.gbl... >> Colin, >> >> Thanks for this answer. Could you elaborate more on this, because I am >> not very good with SQL? Also, the example has 3 levels, but in reality it >> may well be more complicated and deeper - would this break the indexed >> view approach? >> >> jr. >> >> "Colin Dawson" <newsgro***@cjdawson.com> wrote in message >> news:45Wze.66340$G8.22679@text.news.blueyonder.co.uk... >>> Looking at this, there is only a finite number of levels (i.e. 3) A >>> quick answer for is to make use of an indexed view. >>> They work with all versions of SQL Server (except the Desktop Engine I >>> think). However for all versions except enterprise you need to add the >>> NoExpand table hint in order to get SQL to use the index ;-) >>> >>> OK, the basic idea if that you create a massive table which performs the >>> joins, not at select time, but at insert time. However, as it's only >>> ever one row being altered at a time, the amount of overhead to updating >>> the indexed view is alot lower than performing the join every time that >>> you do the select. Also as it's only one row, it won't take long to >>> update. >>> >>> >>> "J Rieggle" <jrieg***@hotmail.co.uk> wrote in message >>> news:OWb7WvJhFHA.3936@tk2msftngp13.phx.gbl... >>>> Hi. >>>> >>>> I am working on an ecommerce website, and I recently implemented paging >>>> of product items from a chosen category. The first approach was to >>>> keep the SQL simple, so I link all products to a single category. >>>> However my client is asking that it be possible to browse all items >>>> that exist within a category, rather than having to click the final >>>> leaf category, E.g. >>>> >>>> Category: Mind, Body, Spirit >>>> Sub-Cat: Spiritualism >>>> Sub-Cat: Meditation Techniques >>>> >>>> Now, if the user clicks Mind, Body, Spirit I need to show ALL products >>>> that fall underneath, including everything from Spiritualism and >>>> Meditation Techniques. This SQL is a snippet of the main query to >>>> return a set of results based on paging - the paging code has been >>>> omitted, and the inner select deals with choosing the correct set of >>>> results to support paging: >>>> >>>> Select P.Name Publisher, Z.ProductID, Z.Name, Z.Authors, Z.Price, >>>> Z.DateOfPub, >>>> LEFT(Z.Description, 500) Description, Z.SaleIndex, Z.ImageLink, >>>> Z.PreviewImageLink >>>> From >>>> Publishers P INNER JOIN ( >>>> Products Z Inner Join ( >>>> Select top 5 A.ProductID >>>> from ( >>>> Select top 5 B1.ProductID, B1.Name >>>> from >>>> Products B1, categoryProducts B2 >>>> where >>>> B2.CategoryID = 1 and B1.ProductID = B2.ProductID >>>> Order By B1.Name ASC >>>> ) A >>>> order by A.Name DESC) >>>> Y On Z.ProductID = Y.ProductID >>>> ) ON P.PublisherID = Z.PublisherID >>>> Order by Z.Name ASC >>>> >>>> First question - there are a number of inner joins going on - is there >>>> a better way to do this? >>>> >>>> In the code above the CategoryID = 1, so if Mind, Body, Spirit had an >>>> ID of 1, then only books with a category of 1 would be chosen, and >>>> books underneath this category would not be chosen. After some >>>> research I have worked out how to set my catgories up to allow a >>>> hierarchical representation. Assuming I have the following table >>>> structures: >>>> >>>> Products >>>> ======== >>>> ProductID >>>> Name >>>> >>>> Categories >>>> ========== >>>> CategoryID >>>> Name >>>> Linkage >>>> >>>> CategoryProducts >>>> ================ >>>> CategoryID >>>> ProductID >>>> >>>> And the linkage column for the categories mentioned are: >>>> >>>> Mind, Body, Spirit = 1.1 >>>> Sub-Cat: Spiritualism = 1.1.1 >>>> Sub-Cat: Meditation Techniques = 1.1.2 >>>> >>>> Then I can find out all books underneath Mind Body and Spirt using: >>>> >>>> SELECT P.* >>>> FROM >>>> products P INNER JOIN >>>> ( >>>> SELECT ProductID >>>> FROM categoryproducts CP >>>> WHERE CP.CategoryID in ( >>>> SELECT categoryID >>>> FROM >>>> Categories >>>> WHERE >>>> Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE >>>> CategoryID=4) >>>> ) >>>> ) A >>>> ON A.ProductID = p.ProductID >>>> >>>> However, to support paging, I need to link this query with the previous >>>> query. I feel there might be too many selects going on when I do this >>>> (but I am no expert), and secondly, I havent got a clue how to >>>> incorporate this query above with the original higher up! >>>> >>>> Help! >>>> >>>> jr. >>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics