Re: stored procedure performance
From: Guoqi Zheng (no_at_sorry.nl)
Date: 07/19/04
- Next message: Enric: "RE: Similar but not exact industry needs"
- Previous message: Hari Prasad: "Re: Logging into DB using isql/osql"
- In reply to: Adam Machanic: "Re: stored procedure performance"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Jul 2004 12:53:35 +0200
Adam,
It is very kind of you. Thank you very much for your help.
SELECT CAST(COUNT(DISTINCT GroupId) AS FLOAT) / COUNT(*) AS GroupPct
Return a value of: 4.9478326341830697E-4
CAST(SUM(isRef) AS FLOAT) / COUNT(*) AS RefPct
Return an error of : The sum or average aggregate operation cannot take a
bit data type as an argument.
I have execute CREATE INDEX idx_GroupId ON tblMsg(GroupId, IsRef) GO
Indeed, it is much much faster now. So I guess that is enough.
To be honest, I am not very clear about the license neither. I asked my
hosting to use Ms Sql 2000, they told me that it costs 5000 Us Dollars. I
think that is some money for me. So we decided to use MSDE. I check
Microsoft site, the limitation of MSDE is Max. 16 Database and max. 2 GB per
database. That can be fine for me. I have another dedicated server with Ms
Sql 2000 in it, so I got a copy of Enterprise manager to use.
Thanks again for your time.
-- Kind regards Guoqi Zheng guoqi AT meetholland dot com http://www.meetholland.com "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:uEQ3FfRbEHA.3524@TK2MSFTNGP12.phx.gbl... > Oops, I forgot about integer math... > > Try this instead: > > SELECT CAST(COUNT(DISTINCT GroupId) AS FLOAT) / COUNT(*) AS GroupPct, > CAST(SUM(isRef) AS FLOAT) / COUNT(*) AS RefPct > FROM tblMsg > --Note I changed isRef into a SUM so we can see if you have a large > percentage of either 1 or 0; if the ratio is closer to 50%, an index will > not be effective. > > Your table has no Primary Key, so you have no indexes. I assume you want > AutoId to be your PK? Use the following SQL: > > ALTER TABLE tblMsg > ADD CONSTRAINT pk_tblMsg > PRIMARY KEY CLUSTERED (AutoId) > GO > > That will create a clustered primary key on your IDENTITY column, which will > make INSERTs faster. But that won't help your query... The following index > should, assuming that GroupID is somewhat unique (see above)... I'm not sure > if adding IsRef will help or not (also see above): > > CREATE INDEX idx_GroupId ON tblMsg(GroupId, IsRef) > GO > > Finally, I don't know what your forum is used for or exactly what the > licenses grant and don't grant, but you should consider buying a copy of SQL > Server Personal or Standard Edition (depending on license)... You'll find it > much easier than trying to use MSDE like that! > > > "Guoqi Zheng" <no@sorry.nl> wrote in message > news:OYHWx4QbEHA.4092@TK2MSFTNGP11.phx.gbl... > > Adam, > > > > Thanks a lot for your quick reply. > > > > I run the statement you gave me, I got a result of 0, 0. And I think it > will > > always be 0, 0 because each group always has over 10,000 records. IsRef > can > > only have value of true/false. > > > > Below is my table. I did not create an index, but I think I have a primary > > key, Ms Sql will create index on that key by itself. Andy suggestion? > > > > > > CREATE TABLE [dbo].[tblMsg] ( > > [AutoId] [int] IDENTITY (1, 1) NOT NULL , > > [MsgId] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [GroupId] [int] NULL , > > [MsgTitle] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [MsgAuthor] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [MsgContent] [varchar] (7020) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [MsgDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [isRef] [bit] NOT NULL > > ) ON [PRIMARY] > > GO > > > > > > Actually, the problem I noticed is that I am using MSDE. My forum is too > big > > (>2GB) for MSDE, so I divided the forum by different groups. so it becomes > 5 > > database with 400 MB per database. If I am only clicking one part of the > > site (using only one database), the first click takes 20 seconds, the next > > clicks will only takes <1 second, I think it is because of cache of stored > > procedure? but if I clicking different part of the site (using > one > > database), every time, I hit a different database, it always takes 20 > > seconds. Am I make myself clear? Any suggestion for me? > > > > -- > > Kind regards > > > > Guoqi Zheng > > guoqi AT meetholland dot com > > http://www.meetholland.com > > > > "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message > > news:OY1u8dQbEHA.1656@TK2MSFTNGP09.phx.gbl... > > > Hi, what indexes are on 'tblMsg', and which are being used by the query? > > > Are you seeing seeks or scans? Actually, please post the table's DDL in > > > full, especially if there are other indexes used for other purposes. > You > > > need to consider all queries against the table when tuning indexes. > > > > > > How unique are the values of GroupId and isRef in the table? Try this > > out: > > > > > > SELECT COUNT(DISTINCT GroupId) / COUNT(*) AS GroupPct, > > > COUNT(DISTINCT isRef) / COUNT(*) AS RefPct > > > FROM tblMsg > > > > > > Remember that for non-clustered indexes to be effective, the first > > column's > > > percentage resulting from this query should be closer to 1 than 0. That > > > might not be true for clustered indexes, though. > > > > > > > > > "Guoqi Zheng" <no@sorry.nl> wrote in message > > > news:%23lKsQ1PbEHA.2816@TK2MSFTNGP11.phx.gbl... > > > > Sir, > > > > > > > > I am a little confused. I have an asp.net application, it is a forum > > site, > > > > in order to have a page navigation, I need to get the value of how > many > > > > pages in total. I used a stored procedure to do this job. However, I > > found > > > > out that this sp always takes about 10 seconds to execute according to > > > data > > > > from profiler. Below is my code, can some one tell me what I have to > > > > improve it? There is about 50,000 records in the table now. > > > > > > > > Thanks in advanced. > > > > > > > > > > > > > > > > CREATE PROCEDURE MyProc_TotalPages > > > > @GroupId int, > > > > @PageSize int, > > > > @r int=0 output > > > > > > > > AS > > > > > > > > Declare @MsgCount as Int > > > > > > > > SELECT @MsgCount = Count(AutoId) FROM tblMsg WHERE isRef=0 and > GroupId > > = > > > > @GroupId > > > > > > > > Declare @TempCount float > > > > set @TempCount = @MsgCount/@PageSize > > > > > > > > set @r = Cast(@TempCount as Int) > > > > > > > > set @r=@r+1 > > > > GO > > > > > > > > > > > > -- > > > > Kind regards > > > > > > > > Guoqi Zheng > > > > guoqi AT meetholland dot com > > > > http://www.meetholland.com > > > > > > > > > > > > > > > > > > > > > > > >
- Next message: Enric: "RE: Similar but not exact industry needs"
- Previous message: Hari Prasad: "Re: Logging into DB using isql/osql"
- In reply to: Adam Machanic: "Re: stored procedure performance"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
