Usung 70% of Micro :(

[josefo123]

Hi!
I'm not into sql programming and I'm having trouble, I got a website where people can check their daily horoscope.
I got a table that contains:
ID (int autoincrement)
Sign (which containd the sign, gemini, taurus, etc)
Text (which contains the text for the sign)
aday (day number)
amonth (month number)
ayear (year number)

I call it thru this stored procedure:
CREATE PROCEDURE [astrodestino].[horoscopos] AS select *
from horoscopo where aday=day(getdate())
and amonth=month(getdate())
and ayear=year(getdate())
GO

When the user enters the site I will execute that stored procedure and I will show the 12 signs and text for that day.
I got 10000 queries per day and my hoting provider told me that its consuming upto 70% of microprocessor resources.
Suspecting that my hosting provider had a problem with his server a moved to other hosting provider and it will happen again.
Is there anyway I can optimize it to lower resource usage?
Im desperate!!
Thank you!

 

[hneal98]

Have you tried just executing the query directly?

 

[gmmastros]

When you do...
Select *

You are getting all the fields from the table. I suspect that your website is only using the sign and Text fields, so....

Alter PROCEDURE [astrodestino].[horoscopos]
AS
select Sign, [Text]
from horoscopo
where aday=day(getdate())
and amonth=month(getdate())
and ayear=year(getdate())

This will limit the amount of data being returned, which will help performance.

If it were my website, I would combine the Day, Month, and Year columns in to a HoroscopeDate column. Then, you can simplify your query to...

Select Sign, [Text]
From Horoscopo
Where HoroscopeDate = GetDate()

At a minimum, I would hope that the aDay, aMonth, and aYear fields are integers. The Day, Month, and Year functions reutrn integers. If those fields are varchar, then SQL Server has to perform conversions to get the data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Please run this query and post the results here.

Select Column_Name, Data_Type 
from   Information_Schema.Columns 
Where  Table_Name = 'Horoscopo'
[code]

This information will make it easier fro us to help you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Do you have access to the SQL Server through Query Analyzer? If you take a look at the execution plan.

I to would put the day, month and year into a single datetime field.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[astrodestino]

Hi! tnx for the replies.
The results of the query were:
id int
aday int
amonth int
ayear int
sign nvarchar
text ntext

 

[SQLDenis]

Try doing this, you might have conversions going on

CREATE PROCEDURE [astrodestino].[horoscopos] AS
set nocount on

declare @day int,@month int, @year int
select @day =day(getdate()),@month =month(getdate()) , @year =year(getdate())

select * from horoscopo where aday=@day
and amonth=@month
and ayear=@year

set nocount off

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Do you have flexibility to modify the table structure?

VarChar fields can store a maximum of 8000 bytes. If the data in your text field is less than 8000 bytes for each record, I recommend you change it to a varchar field.

I also recommend that you combine the dates in to a single field. You can index this field and performance should increase dramatically. If you can't change the table structure, you should at least make sure that the day, month, and year fields are indexed.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Whatever you do, this sproc will be called 10000 times every day. So...

Can you somehow schedule/generate all 12 daily horoscopes, save their HTML fragments to disk, then include these files on web pages? This is standard approach for hi-load sites - number of repeatable queries gets down to minimum and web servers are typically optimized for fast file access anyway.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[druer]

Excellent idea on pre-loading the results. Short of storing the HTML, you could simply schedule a job that runs at 12:00:01 AM or something that loads the 12 values into another table, and then simply have this high volume stored procedure load its data from this new table which only has 12 rows.

Also assuming you do nothing else, be sure you have a three column index on the table for year, month, day fields. It could be you are doing a table scan every single time you are doing this, or if you have a single column index or something, or have the fields as day, month, year or something it would still have to do an index scan.

Just some thoughts,