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
...
> 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
Patrick F wrote:
> 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
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
