45 queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

45 queries

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-23 : 01:17:37

Ok this might be a really dumb question but I'll ask it anyways, I'm just starting to learn SQL .. please bear with me

I have a page that is hit often and has way too many queries on it than needed, so I am going to set these 45 values into an application variable.

Is there anyway to have a SP with 45 select statements or do I have to create 45 recordsets???

Thanks again!

Mike

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-23 : 01:52:22

It sounds like what you should be doing is calling a stored procedure which builds a table of your 45 values by doing various queries, and returns that table as your recordset.

Is that what you're trying to find out?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-23 : 02:09:49

I would like to get the 45 values into application variables to speed things up. Thanks I think that will work although may be prove difficult for me to do. Any other insight?, anyone?

Thanks :)

Edited by - mike123 on 01/23/2002 02:10:41

Nazim
A custom title

1408 Posts

Posted - 2002-01-23 : 02:12:50

45 Queries!! your database design doesnt look good enough. as you are still in start try improvising on it.

Coming to your question. you can unioun all 45 selects in your sp to get all the values. using 45 no. of recordsets will be a big performance bottleneck.

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 01/23/2002 02:13:55

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-23 : 03:07:35

im actaully working with SNITZ messageforums ... you are probably familiar with it. the main page gets the count of all the posts and topics for each forum..... 22 forums later we have alot of queries

thanks again, I will read up on unions

Edited by - mike123 on 01/23/2002 03:08:43

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-23 : 03:10:23

actually this is run on snitz!

I read once in an article that the default page for snitz ran 110 queries??? Seems ridiculous...

Nazim
A custom title

1408 Posts

Posted - 2002-01-23 : 03:14:55

Nopes! i think i made a fool of myself coz without actually going thru the requirements you really cant judge about the no. of queries requied .

Sorry for that.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-23 : 12:24:24

graz wrote and article on tuning the Snitz forums for SQL Team:

http://www.sqlteam.com/item.asp?ItemID=6891

There's also a Snitz forum thread that he linked in. It's mostly analysis, I'm not sure how much code he provided, but it should give you a lot of insight into the database resources used.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-24 : 01:29:06

Can anybody give me a little guidance on how to do this ??

I'm a little lost trying to insert 45 different values from 45 queries into a (temp?)table

Cheers!

Nazim
A custom title

1408 Posts

Posted - 2002-01-24 : 01:37:31

insert into #temp
select columnname from table1
union
select columnname from table2
union
select coumname from table3.....

but coming to your requiremnts. if you are using this for a forum , how will you determine from which query does a value come from.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-24 : 01:55:43

thanks nazim, let me clear myself up a bit here

select count(cat_ID) as 1_topics from forum_topics where forum_id='11'
select count(cat_ID) as 2_topics from forum_topics where forum_id='12'
select count(cat_ID) as 3_topics from forum_topics where forum_id='13'
select count(cat_ID) as 4_topics from forum_topics where forum_id='15'
select count(cat_ID) as 5_topics from forum_topics where forum_id='21'

I have 45 of these statements to run inside the SP. From the ASP page I would like to retrieve statements value, so I am wondering the same thing how can I do this ??

cheers

Nazim
A custom title

1408 Posts

Posted - 2002-01-24 : 02:02:59

select cat_id,count(cat_id) from tablename
where cat_id in('13','14','45')
group by cat_id

shouldnt this help you.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-24 : 04:19:54

CREATE PROCEDURE select_forumtotals

AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempForumCount
(
1_topics int,
2_topics int,
3_topics int,
4_topics int,
5_topics int,
6_topics int,
7_topics int,
8_topics int,
9_topics int,
10_topics int,
11_topics int,
12_topics int,
13_topics int,
14_topics int,
15_topics int,
16_topics int,
17_topics int,
18_topics int,
19_topics int,
20_topics int,
21_topics int
)
-- Insert the join info into the table
INSERT INTO #TempForumCount (1_topics, 2_topics, 3_topics, 4_topics, 5_topics, 6_topics, 7_topics, 8_topics, 9_topics, 10_topics, 11_topics, 12_topics, 13_topics, 14_topics, 15_topics, 16_topics, 17_topics, 18_topics, 19_topics, 20_topics, 21_topics)

select count(cat_ID) as 1_topics from forum_topics where forum_id='18'
UNION
select count(cat_ID) as 2_topics from forum_topics where forum_id='20'
UNION
select count(cat_ID) as 3_topics from forum_topics where forum_id='4'
UNION
select count(cat_ID) as 4_topics from forum_topics where forum_id='7'
UNION
select count(cat_ID) as 5_topics from forum_topics where forum_id='8'
UNION
select count(cat_ID) as 6_topics from forum_topics where forum_id='5'
UNION
select count(cat_ID) as 7_topics from forum_topics where forum_id='10'
UNION
select count(cat_ID) as 8_topics from forum_topics where forum_id='26'
UNION
select count(cat_ID) as 9_topics from forum_topics where forum_id='23'
UNION
select count(cat_ID) as 10_topics from forum_topics where forum_id='9'
UNION
select count(cat_ID) as 11_topics from forum_topics where forum_id='27'
UNION
select count(cat_ID) as 12_topics from forum_topics where forum_id='11'
UNION
select count(cat_ID) as 13_topics from forum_topics where forum_id='12'
UNION
select count(cat_ID) as 14_topics from forum_topics where forum_id='13'
UNION
select count(cat_ID) as 15_topics from forum_topics where forum_id='15'
UNION
select count(cat_ID) as 16_topics from forum_topics where forum_id='21'
UNION
select count(cat_ID) as 17_topics from forum_topics where forum_id='14'
UNION
select count(cat_ID) as 18_topics from forum_topics where forum_id='22'
UNION
select count(cat_ID) as 19_topics from forum_topics where forum_id='16'
UNION
select count(cat_ID) as 20_topics from forum_topics where forum_id='24'
UNION
select count(cat_ID) as 21_topics from forum_topics where forum_id='28'

GO

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-24 : 04:21:14

Hmmm, I am getting this error... it looks fine to me whats up with that????.. How does it look .thx again

mike

Server: Msg 120, Level 15, State 1, Procedure select_forumtotals, Line 37
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Teroman
Posting Yak Master

115 Posts

Posted - 2002-01-24 : 06:00:39

If you ran your select you would see it only selects 1 column, and your temp table has loads of them

id go with a previous suggestion

select count(cat_ID)
from forum_topics
where forum_id
group by forum_id
order by forum_id

although this may not work with the snitz asp if it wants seperate recordsets, not 1 with several rows

if you can rework the asp you'll be fine though, just do

rs.MoveNext()

instead or set rs = rs.nextRecordset

at a guess, ive not seen the code!

col

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources