Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

mssql 2005 problem, to many requests, need to optimize


Hello, I have a bit of a problem. Some of my pages has way to many database
requests, some pages upto 70 requests,
and the loading time can be all the way up to 12seconds because of the many
requests. So i am trying to optimize it abit.
What i want to know if how to send several ID's to the database and collect
the result from all of the ID's, i have a stored procedure like this:

public DataTable User_GetUsernameTypeTwo(string strUsername)
{
OpenDBCon();
mCom.Parameters.Clear();
mCom.CommandText = "Profile_GetUsernameTypeTwo";
mCom.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar, 20));
mCom.Parameters["@UserName"].Value = strUsername;
myAdap = new SqlDataAdapter();
myTable = new DataTable();
myAdap.SelectCommand = mCom;
myAdap.Fill(myTable);
CloseDBCon();
return myTable;

}

To this i send a username and in return i get info about the user that then
is presented on the page. If i have a gridview on the page with 20 posts,
then it needs to make 20 requests to this file in order to collect all the
info for all the users. Then i have one just like the one above that collects
the photos of all the users. So my first thought was that i merge these two
together, witch works perfect, reduces the database calls by around 40%. Then
i thought, perhaps can i get all the info from all the users in 1 request, to
reduce the load on the database even more, my problem is: How can i rewrite
the one above to accept lets say 50 usernames (1 username can max have
20characters), and return the info from all of them. Since i use a stored
procedure i cant with ease use WHILE (username = xxx) AND (username = yyy)
AND (username = ccc), then i would have to add 50 Parameters and add each
username to a seperate parameter and then look if the parameter is null or if
it contains a username. So i need to find another way to send the usernames,
so the sqlserver can add them to the where clause, perhpas using IN?

This is the sql server store procedure (it works perfect, when its one user
i am requesting info about), i am using FULL OUTER JOIN because ALL fields i
request can be NULL:

ALTER PROCEDURE [dbo].[Profile_GetUserpopupInfo]
-- Add the parameters for the stored procedure here
@UserName nvarchar(20)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT settings_username.color, settings_username.pic1,
settings_username.pic2, profile_profilephoto.imageurl,
profile_profilephoto.alttext FROM settings_username FULL OUTER JOIN
profile_profilephoto ON profile_profilephoto.username =
settings_username.username AND (profile_profilephoto.approved = 1) WHERE
(settings_username.username = @UserName)
END

Anyone got any idea of how to send a bunch of usernames to this stored proc?
(it must be efficient since there is alot of users)

Patrick

> Anyone got any idea of how to send a bunch of usernames to this stored
> proc?
> (it must be efficient since there is alot of users)

See Erland's article on arrays/lists:
http://sommarskog.se/arrays-in-sql.html.  In SQL 2005, you also have the
option of passing XML that can be easily transformed into relational data
for joins.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Patrick F" <Patri@discussions.microsoft.com> wrote in message

news:7BAC7828-450A-4101-A564-2675F153D482@microsoft.com...

The first step would be to make sure the query inside the stored
procedure is optimized.  Review the execution plan, look for missing or
ineffective indexes, they'll show up as table scans, index scans, and/or
bookmark lookups.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc