Any way to optimize sp_sproc_columns

We just upgraded from SQL 6.5 to SQL 2000. We also have moved SQL Server to
a much beefier machine. Actual data access in our database is much faster,
but we've noticed that the speed of the application itself has slowed down
some because sp_sproc_columns now seems to take quite a bit longer to run
than it did in 6.5. It's really apparent in portions of our app where we
issue several consecutive calls to the DB in rapid succession. We're using
RDO to access the database, and right now changing that is not a viable
option. Does anyone have any ideas how to either tweak sp_sproc_columns, or
replace it with something more efficient?
Thanks in advance,
BrettMaybe you could adapt something like this?
http://www.aspfaq.com/2463
"Brett" <do.not.spam.me.you.jerks.maelstrom1335.NO.s.p.a.m@.swbell.net> wrote
in message news:Om6k29BYDHA.1748@.TK2MSFTNGP12.phx.gbl...
> We just upgraded from SQL 6.5 to SQL 2000. We also have moved SQL Server
to
> a much beefier machine. Actual data access in our database is much
faster,
> but we've noticed that the speed of the application itself has slowed down
> some because sp_sproc_columns now seems to take quite a bit longer to run
> than it did in 6.5. It's really apparent in portions of our app where we
> issue several consecutive calls to the DB in rapid succession. We're
using
> RDO to access the database, and right now changing that is not a viable
> option. Does anyone have any ideas how to either tweak sp_sproc_columns,
or
> replace it with something more efficient?
> Thanks in advance,
> Brett
>

any tips for testing a san?

we finally got our san going here on a test db server.
any tips on testing it?
old disks are raid 5, san is configured raid 5.
so far i've moved some db's data and log files to the san. no problems.
changed the backups to point to the san.
saw significant improvements in backup times.
no improvement on full dbreindex.
nearly all of my databases are small (50mb to 500mb) which sort of
prevents large scale testing. i've got one 5gb db that i'll move over
(should be able to get some good read tests from it). i've also got a
sql stress test tool that i downloaded from somewhere (forget the name
of it though) that inserts millions of records and then reads them.
any other ideas or things to watch out for?when we first implemented SAN we were told to limit size
of individual files to 40gb, since then we have made sure
to set maximum growth of data / log files to 40gb, and add
additional files as needed. I am not sure if this is the
limit of SAN or Windows operating system or sql server.
Out experience is that if you have very large files then
you may have issues restoring them.
We never had any significant issues with SAN, and I am
glad that burden of managing 30+ spindles and few disk
cages is off my shoulders.
hth.
>--Original Message--
>we finally got our san going here on a test db server.
>any tips on testing it?
>old disks are raid 5, san is configured raid 5.
>so far i've moved some db's data and log files to the
san. no problems.
>changed the backups to point to the san.
>saw significant improvements in backup times.
>no improvement on full dbreindex.
>nearly all of my databases are small (50mb to 500mb)
which sort of
>prevents large scale testing. i've got one 5gb db that
i'll move over
>(should be able to get some good read tests from it).
i've also got a
>sql stress test tool that i downloaded from somewhere
(forget the name
>of it though) that inserts millions of records and then
reads them.
>any other ideas or things to watch out for?
>
>.
>

ANSII NULL AND QUOTED IDENTIFERS - Upgrade problems

I inherited a system that was started in Access and moved to SQL 2000. The business has grown and we are trying to replace our older systems with ASP.NET and Server 2005.

Currently, we are trying to make a new asp.net page for searching the database for records with matching dates or date ranges. There are several types of dates to search, so they are all optional. Set to default as null in the proc. For each date there is an operator field, such as equal or greater, etc. The proc only looks at the date if the operator is set to EQ" or "IN" and ignores the date if operator set to "NO"

The proc works fine when running under Management Studio, but fails coming through a SQLDataSource to a gridview. It works with integer and string filters, but fails when entering the same date ('07/20/2007') that works in the testing tool. All dates are actually stored as datetime, and they are set as DateTime Control Parameters in the SQLDataSource.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:someConnectionString %>"

SelectCommand="spTESTSearch"SelectCommandType="StoredProcedure">

<SelectParameters>

<asp:ParameterDefaultValue="M"Name="TypeCode"Type="String"/>

<asp:ParameterDefaultValue="EQ"Name="FirstPubOp"Type="String"/>

<asp:ControlParameterControlID="FirstPubDateTextBox"DefaultValue=""Name="FirstPubDate"

PropertyName="Text"Type="DateTime"/>

<asp:ParameterDefaultValue="C"Name="UserName"Type="String"/>

<asp:ParameterDefaultValue="NO"Name="SearchTextOp"Type="String"/>

<asp:ParameterName="SearchText"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

The dates are selected properly in the testing tool, with code such as :

DateDiff(day, FirstPubDate, @.FirstPubDate)= 0

I think my problem is based on option settings for the databases themselves. The old database was set to Ansii Nulls and Quoted Identiers to OFF, and the new ones were defaulted to them being ON. I noticed that the tool also, sets those options on when creating new stored procedures.

Would this difference be causing the dates to be quoted and viewed as objects rather than strings? What are the dangers in changing those options on the database that still gets uploads from the old SQL 2000 database and some Mac-based systems?

I welcome any suggestions on how to get my new stuff running while not breaking my old production systems.

Thanks for the assist!

The options you mentioned should have no relevance to your current issue. Quoted identifiers is referring to whether a statement like:

SELECT "my_column" or SELECT [my_column] is the correct way to specify column/table names that have characters in them that could cause confusion, or are reserved words.

The ANSI NULLS parameter refers to a number of things like if the boolean operation (NULL=NULL) should return true, or NULL.

BTW, the default values on the stored procedure (as specified in the stored procedure) aren't used. They only come into play when you call a stored procedure without specifying a value for that parameter. The parameters you have defined in the SqlDatasource will always pass a value.

Personally, I would first hook up an event to the SqlDatasource_Selecting event. By checking the e.Command.Parameters collection in that event, you can see what it about to be passed to the stored procedure. Verify that passing the values (as they were in the selecting event) works in Management Studio.

If that does not lead you to the problem, then run Sql Profiler, and compare the difference between the queries that get submitted via the web application, and the query submitted by management studio.