A quick SQL question - GoFuckYourself.com

Bad B0y · 11-05-2002, 02:17 PM

Say for example you have 10 000 products in a table and they all fall into 30 categories.

Now you want to select 50 products from a couple of those categories (could be any amount between 1-30 of those categories pending on how many the user wants).

Do you,
A: make one select query to get all 10 000 products and filter the results yourself.
or
B: make one select query per category(meaning 1-30 queries) and limit each query to 50.

Which would be more efficient?

Machete_ · 11-05-2002, 02:26 PM

If on train going 40 mph. gets a 30 min headstart, when will the train going 55 catch up?

Alky · 11-05-2002, 02:30 PM

B as long as your query is efficient.

grumpy · 11-05-2002, 02:54 PM

B and i dont thinks you wanna show 1000 products at the same time... 20 cats times 50 products .....so B

AcidMax · 11-05-2002, 02:56 PM

OR depending on your skill level you use joins and such and if your tables are setup right you might be able to pull all the information you need in a single query with GROUP BY and other similar SQL functions. Do reasearch about the DB type and understand when and when not to use database normalization.

All really depends on the layout and what you are trying to get specifically from it.

Andy

Bad B0y · 11-05-2002, 03:00 PM

yeah. think i'll write both functions and run 'em up against each other.

and yes i do have to much time on hand

I need a new project after the succesful launch of http://webmasters.za.net

Bad B0y · 11-05-2002, 03:11 PM

ok. you have all the products along with all the category id's in one table. so you want to select 50 products per categoryid, but it's not that easy.

Penrod · 11-05-2002, 03:13 PM

Quote:

Originally posted by ebus_dk
If on train going 40 mph. gets a 30 min headstart, when will the train going 55 catch up?

not long

Rory · 11-05-2002, 03:23 PM

You seem to keep asking questions and its a bit uncclear exactly what you want. However keep in mind one thing ..... let sql do the work as opposed to PHP,Perl, etc. Dont pull out all the data , then sort - you want to sort , etc. then pass the relevant data into your program. You will always get better results if you do the sorting, etc. within sql. If you cant create a query for it, you will need to go back and redesign your db (write it down first , dont create as you go). If you wing it you will run into issues down the road , be they performance, normalization, etc.

Rory

Bad B0y · 11-05-2002, 03:31 PM

thanks allot guys.

i was a bit unsure about 20 queries or one hughe one.

11-05-2002, 02:17 PM	#1
Bad B0y Confirmed User Join Date: Aug 2002 Posts: 160	A quick SQL question Say for example you have 10 000 products in a table and they all fall into 30 categories. Now you want to select 50 products from a couple of those categories (could be any amount between 1-30 of those categories pending on how many the user wants). Do you, A: make one select query to get all 10 000 products and filter the results yourself. or B: make one select query per category(meaning 1-30 queries) and limit each query to 50. Which would be more efficient?

11-05-2002, 02:54 PM	#4
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	B and i dont thinks you wanna show 1000 products at the same time... 20 cats times 50 products .....so B __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888

11-05-2002, 02:56 PM	#5
AcidMax Confirmed User Join Date: May 2002 Location: MI Posts: 1,827	OR depending on your skill level you use joins and such and if your tables are setup right you might be able to pull all the information you need in a single query with GROUP BY and other similar SQL functions. Do reasearch about the DB type and understand when and when not to use database normalization. All really depends on the layout and what you are trying to get specifically from it. Andy __________________ Latest MMA news. http://www.mmawrapup.com

11-05-2002, 03:23 PM	#9
Rory Confirmed User Join Date: Jul 2002 Location: I Love Ixtapa, Mexico ö Posts: 616	You seem to keep asking questions and its a bit uncclear exactly what you want. However keep in mind one thing ..... let sql do the work as opposed to PHP,Perl, etc. Dont pull out all the data , then sort - you want to sort , etc. then pass the relevant data into your program. You will always get better results if you do the sorting, etc. within sql. If you cant create a query for it, you will need to go back and redesign your db (write it down first , dont create as you go). If you wing it you will run into issues down the road , be they performance, normalization, etc. Rory __________________ Promote a site that actually retains members!

11-05-2002, 02:26 PM	#2
Machete_ WINNING! Industry Role: Join Date: Oct 2002 Posts: 14,579	If on train going 40 mph. gets a 30 min headstart, when will the train going 55 catch up?

11-05-2002, 02:30 PM	#3
Alky Confirmed User Join Date: Apr 2002 Location: Houston Posts: 5,651	B as long as your query is efficient.

11-05-2002, 03:00 PM	#6
Bad B0y Confirmed User Join Date: Aug 2002 Posts: 160	yeah. think i'll write both functions and run 'em up against each other. and yes i do have to much time on hand I need a new project after the succesful launch of http://webmasters.za.net

11-05-2002, 03:11 PM	#7
Bad B0y Confirmed User Join Date: Aug 2002 Posts: 160	ok. you have all the products along with all the category id's in one table. so you want to select 50 products per categoryid, but it's not that easy.

11-05-2002, 03:31 PM	#10
Bad B0y Confirmed User Join Date: Aug 2002 Posts: 160	thanks allot guys. i was a bit unsure about 20 queries or one hughe one.