bizzydint Posted May 29, 2003 Author Posted May 29, 2003 I am not permitted to show any data/code/sql as I am dealing with sensitive information (NB: authors/books was just a simplified example) jfackler - right join didnt work. thanks for the suggestion though ;-) wyrd - no offense, but it might be time for you to give up. I really cant explain the problem more than I have! sorry... I've been thinking it through again, and I may have a solution that will do - it uses 2 queries, one to pull out the "books" and then one to pull out the "authors" of those "books". Not as efficient, but I cant seem to find another way.... ANyone know how to pass a column from a DataTable to a db SP? query 1 = select bookName, Author_iD form Books where "blah" query 2 - select DISTINCT AuthorName from Authors where Author_ID in (results from query 1) I only want query 1 to be called once!! Alternatively, is there a way of selecting the DISTINCT authors from the DataTable??? Really appreciate all the effort you guys are making.... But *please* stop insisting that I'm male ;-) Grant me the serenity to accept the things I cannot change, the courage to change the things I cannot accept and the wisdom to hide the bodies of those people I had to kill today cos they pi**ed me off.
wyrd Posted May 29, 2003 Posted May 29, 2003 (edited) I am not permitted to show any data/code/sql as I am dealing with sensitive information (NB: authors/books was just a simplified example) As you notice I said sample data. Who says it has to be the actual data you're using? wyrd - no offense, but it might be time for you to give up. I really cant explain the problem more than I have! sorry... *shrug* ... Well if you don't want my help you don't have to read my posts. ;) query 1 = select bookName, Author_iD form Books where "blah" query 2 - select DISTINCT AuthorName from Authors where Author_ID in (results from query 1) I only want query 1 to be called once!! SELECT DISTINCT AuthorName FROM Authors WHERE Author_ID IN (SELECT BookName, Author_ID FROM books WHERE 'blah') Once again this probably isn't what you want, but it's at least in one query. BTW why is DISTINCT needed? Shouldn't author names already be unique? Alternatively, is there a way of selecting the DISTINCT authors from the DataTable??? I don't believe you can, you need to query the database to get DISTINCT authors from a table. Really appreciate all the effort you guys are making.... Oh? But I thought you wanted me to give up... If you aren't finding us of much help, go to the news group microsoft.public.sqlserver.programming and post there (tons of professionals who do nothing but SQL programming go to this). Be warned though; unless you're willing to show them what your table schema is and sample data and a sample of what you want selected, you may not find much help. Edited May 29, 2003 by wyrd Gamer extraordinaire. Programmer wannabe.
bizzydint Posted May 30, 2003 Author Posted May 30, 2003 Right - am making decent progress on all this on my own now, but will attempt to give fictional data/structure again....just incase you can come up with any bright ideas. Tbl_Author author_id INT author_name NVARCHAR author_otherInfo DATATYPE tbl_Books book_ID Book_Name author_id book_genre INT book_price book_something The program wants to display a certain set of books, eg depending on book_genre If you pull it all out of one query and display it, it comes out as: Author 1 Book A Author 1 Book B Author 2 Book C Author 3 Book D Author 3 Book E To make it look nicer, we want: Author 1 Book A Book B Author 2 Book C Author 3 Book D Book E As no one could come up with a way of generating that from one set of query results - I'm using 2 (required for the control to work - not cos i cant do a nested select!) - One to pull out the books - One to pull out the author's of *those* books However - it's a bit inefficient. I'm pulling out the books. Then concatenating the AUTHOR_IDs into a string which I pass to the 2nd query. Is there a way of just chucking the results of the first query straight to the second one??? I cannot (repeat *cannot*) run the "getBooks" query again in the "getAuthors" query - it's far too chunky to be done twice. I've said all this before. Chances are wyrd will still find it vague ;-) Grant me the serenity to accept the things I cannot change, the courage to change the things I cannot accept and the wisdom to hide the bodies of those people I had to kill today cos they pi**ed me off.
wyrd Posted May 30, 2003 Posted May 30, 2003 Okay, now I understand. The master/detail form is what you want, unfortunately it's not a single select statement. I do not know how to do this in a singe select statement (or even if that's possible). URL of live demo of master/detail: http://aspnet.4guysfromrolla.com/demos/MasterDetail.aspx From the sounds of it, your two selects should probaby be; SELECT * FROM authors ORDER BY name SELECT * FROM books WHERE author_id='?' ORDER BY name I'm still confused as to why you need DISTINCT. From the table design you showed me, authors should be unique to begin with. If it's sluggish then you need to change your design a little bit (or do some performance tuning on your database). And don't be rolling your eyes at me either, there shouldn't be any reason for two simple select statements to be sluggish. Idea: - Put authors in a drop down list. If there are tons of authors then you can seperate them further into "pages" of A-Z. User clicks B, the drop down fills up with all authors names starting with B. - When the user selects an author from the drop down list, select the books that belong to that author into either a data grid or data list and display them. - Be sure to use paging for the book list. The layout should look something like this (which slightly resembles the output you showed me, just one author at a time) Selected Author (Drop down list of authors) (DataGrid or DataList of books of selected author) Selected Author Book 1 Selected Author Book 2 Selected Author Book 3 etc... Quick index tips to speed up queries: - If you are going to select books and/or authors by name 90%+ of the time (ORDER BY name), then make the name column the clustered index for the table. This will improve performance for large selections drastically. - Make sure you have a foreign key relationship with the author_id column in books to the author_id column in authors. This can improve performance for JOINs. - Make sure the author_id and book_id are marked as primary keys. Gamer extraordinaire. Programmer wannabe.
dsgreen57 Posted June 2, 2003 Posted June 2, 2003 I'm with wyrd on this one, sounds like your database needs performance tuning if its sluggish because of too small queries. I can return a massive resultset with no problem, as the table has been indexed correctly. Too carry out in one query try using xml, and then populate the results you want from that. As to everything else it looks like you need to decide on the display mechanism you want, suggestions by jfackler look like what you describe, if you can't do exactly what you need just inherit a datagrid and make it do what you want or make your own control.
bizzydint Posted June 2, 2003 Author Posted June 2, 2003 You dont seem to have caught onto the fact that I've been simplifying the query so that it makes sense to you. The *actual* problem doesnt even involve books and authors! The first query actually does: SELECT Author_ID, tbl_Books.book_id, tbl_Book.Book_Name FROM tbl_Books WHERE author_ID IN (' +@comparisonString + ') AND Book_Criteria1 IN (' + @criteriaMatch + ') AND Book_Criteria2 IN (' + @criteriaMatch + ') AND Book_Criteria3 IN (' + @criteriaMatch + ') AND Book_Criteria4 IN (' + @criteriaMatch + ') AND ( Book_Criteria5 = ' + @ID + ' OR Book_Criteria6 = ' + @ID + ' OR Book_Criteria7 = ' + @ID + ' OR Book_Criteria8 = ' + @ID + ' ) ORDER BY Author_ID') Where @comparisonString is a list of integers, and @criteriaMatch is a list of strings (~300 char long) TBl_Books currently contains 82645 rows - and we're still adding to it. It'll probably get up to 100,000 rows. We may also have several 1000s of users running these queries at the same time. SO even with very efficient DBing, you can see why I'm trying to cut down elsewhere. Grant me the serenity to accept the things I cannot change, the courage to change the things I cannot accept and the wisdom to hide the bodies of those people I had to kill today cos they pi**ed me off.
dsgreen57 Posted June 2, 2003 Posted June 2, 2003 bizzydint it comes down to efficient design, you need to think how many master and child records can they see at a time. Not 82645! If you write your query correctly and your database is indexed properly you can return this information in I estimate under a second. It seems the way you are trying to accomplish this is extremly inefficient. I'm guessing you get results of the first query and go through each one and the associated books. No wonder you are experiencing problems.... Set a number of authors they can see at any one time, use paging on your DataGrid, and only query the books when the user requests to see that information. If you really need to see everything in one go, use xml.
bizzydint Posted June 2, 2003 Author Posted June 2, 2003 but the authors that get displayed depend on the books meeting the criteria. so i cant limit the authors first.... Grant me the serenity to accept the things I cannot change, the courage to change the things I cannot accept and the wisdom to hide the bodies of those people I had to kill today cos they pi**ed me off.
dsgreen57 Posted June 2, 2003 Posted June 2, 2003 So let me get this right; the user queries a book and get's an author back strange (very stange) ah well.
wyrd Posted June 2, 2003 Posted June 2, 2003 (edited) You dont seem to have caught onto the fact that I've been simplifying the query so that it makes sense to you. The *actual* problem doesnt even involve books and authors! :mad: *extremely disgusted* You ask our help based on information you give us, yet you don't even give us the correct information in which you need help on. How exactly did you expect us to help you? :confused: Now since you've shown us what you're really trying to do, could you show us what the following is AND give us an example of what it can be based on a fictitional search; @comparisonString @criteriaMatch @ID Book_Criteria1-8 (if these are table columns then their names are way too cryptic) In the mean time, you can speed up IN by providing values in ascending order. Just to make sure I understand; You're searching for books that meet a certain criteria, the displaying the authors for those books (but only displaying the authors name once if they happen to be the author of multiple books found) With only 100k rows, you shouldn't be getting any performance hits at all. I'd hate to see what happens when you're list reaches 1 million. Edited June 2, 2003 by wyrd Gamer extraordinaire. Programmer wannabe.
dsgreen57 Posted June 3, 2003 Posted June 3, 2003 (edited) OK, read this entire topic four times now, and come to the conclusion bizzydint must be doing something drastically wrong. Looking at the first query and simplifying aside You dont seem to have caught onto the fact that I've been simplifying the query so that it makes sense to you. The *actual* problem doesnt even involve books and authors! Your query shouldn't take long, I work for a software house developing financial solutions and implemented the sort of thing you want on a table of 1/2 million records, properly indexed and it doesn't take excessive amounts of time. You may find better performance if you don't ask the database server to order by AuthorID as the table will be ordered by BookID (dependent on how optimised) and that could affect query. Second query with your list of AuthorID why do you need to query again what else do you need the name get in the first query. OK if you can't do that how many records can they see at a time 50...100...150 doubt it; don't get all the authors. Say your query returned 1000 author ID and you sent all of them to the second query (how long would that take) unless.... you are querying the database authorID by authorID... now that would cause major problems (please say your not doing that!) If the database is slow there are ways of splitting a nested query into two DataSets programatticaly. [optimising] Say you could see 50 records and you performed a query on the database to get the name of 50 authors how long would that take 1/2 second at the most! Out of interest you say your getting slow performance how slow? What does profiler say you are getting for the first query? Anything more than 5 seconds you are doing something wrong [complicated database aside] the tests I performed were on a complex financial database. Edited June 3, 2003 by dsgreen57
bizzydint Posted June 3, 2003 Author Posted June 3, 2003 with the customers i deal with, 5 seconds is too long ;-) (yes, i know...but you cant argue with them!) And now i'm going to put a stop to this thread (on my part at least). I'm not getting any help from it anymore, and it's wasting all of our time.... Thanks for trying. Grant me the serenity to accept the things I cannot change, the courage to change the things I cannot accept and the wisdom to hide the bodies of those people I had to kill today cos they pi**ed me off.
wyrd Posted June 3, 2003 Posted June 3, 2003 And now i'm going to put a stop to this thread (on my part at least). I'm not getting any help from it anymore, and it's wasting all of our time.... We gave you quite a bit of help, it's up to you on whether you make use of it or not. If you would of fully explained from the beginning we could of given you even more. *shrug* Good luck with your project. Gamer extraordinaire. Programmer wannabe.
Recommended Posts