An update query

Hi,
I run a same update query in our two servers, which have the same
configuration and same database and indexes, one of them is returning the
result in 4 seconds, and the other one in 7 minutes. the only difference
between them is that the fast server has 512 MB and the other one has 256 MB
RAM. Why is it that much slow.
Thanks in advance,
Mathew
hi Mathew,
Mathew wrote:
> Hi,
> I run a same update query in our two servers, which have the same
> configuration and same database and indexes, one of them is returning
> the result in 4 seconds, and the other one in 7 minutes. the only
> difference between them is that the fast server has 512 MB and the
> other one has 256 MB RAM. Why is it that much slow.
> Thanks in advance,
actually the 2 servers are not the same... probably they do not have the
same data too, and/or the same disk subsystem... perhaps the second is more
fragmented too, both at physical OS file status and at internal logical page
status..
and having the half of RAM does not help for sure.., this mean MSDE has
fewer available resources, that the runnig applications have more
contentions for resources with the SQL Services...
more paging at OS level will be needed by all applications... the whole
system can be involved...
BTW, the 2 results are very different indeed... but try cleaning up your
server... defrag it, both at OS level and database level..
try comparing the statistics output as well as the used plans...
lot of variables are involved...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Am I doin this right? SELECT / ExecuteNonQuery statement

I am working on a web service method that will return weather or not a page url is stored in the database but the ExecuteNonQuery keeps returning -1. I was just wondering if i was doing anything wrong or why the ExecuteNonQuery method does not return a value of 1 or more indicating that the pageurl exists in the database? I have tried using the SQLDataReader as well to no effect and I have verified that SELECT statement refers to valid table and field names. Any help or pointers would be appreciated. I'm still kind of a newb when it comes to db programming.

1 <WebMethod()> _
2Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
45 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
78Dim cmdAs String9 cmd ="SELECT [" & Constants.SourceFieldName &"] "10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase
1516 conn.Open()
1718Dim existsAs Boolean =False19 exists = (C.ExecuteNonQuery > 0)
2021 conn.Close()
22 C.Dispose()
23 C =Nothing24 conn.Dispose()
25 conn =Nothing
2627 Return exists
28End Function29

ExecuteNonQuery:Runs theAdomdCommand without returning any results.

For the purpose I understand, you can use ExecuteReader or ExecuteScalar if you required only one column value is return.

I think you have to use. ExecuteScalar.

Please make sure you click the Answer button if this is true answer.

Regards.

|||

In addition to what the prior person mentioned, if all you want to know is that the record exists then query for a count. SELECT COUNT(primaryKey) FROM tblName WHERE .....<add all your where conditions here>. Then you can use ExecuteScalar - much more efficient.

|||

<WebMethod()> _
2 Public Function IsPageStored(ByVal pageurlAs String)As Boolean
3 If String.IsNullOrEmpty(pageurl)Then Return False
4
5 Dim connAs New SqlConnection()
6 conn.ConnectionString = ConfigurationManager.ConnectionStrings("dbStoredList").ConnectionString
7
8 Dim cmdAs String

cmd="IF EXISTS("
9 cmd &="SELECT [" & Constants.SourceFieldName &"] "
10 cmd &="FROM [" & Constants.StoredCopyTableName &"] "
11 cmd &="WHERE ([" & Constants.SourceFieldName &"] ='@.Source')"

cmd &= ") SET @.RETVAL=1 ELSE SET @.RETVAL=0"
12 Dim C As New SqlCommand(cmd, conn)
13 C.Parameters.AddWithValue("@.Source", New SqlTypes.SqlString(pageurl))
14 C.Parameters.Item("@.Source").CompareInfo = SqlTypes.SqlCompareOptions.IgnoreCase

c.Parameters.Add("@.RETVAL", SqlDbType.Int).Direction = ParameterDirection.Output

15
16 conn.Open()
17
18 Dim existsAs Boolean =False

c.ExecuteNonQuery
19 exists = c.Parameter("@.RETVAL").Value
20
21 conn.Close()
22 C.Dispose()
23 C =Nothing
24 conn.Dispose()
25 conn =Nothing
26
27 Return exists
28 End Function

Using the EXISTS allows SQL Server to stop retrieving records when it finds the very first record that matches, instead of having to find them all. This also should force SQL Server to optimize it's query plan.

I've also changed the code from the above examples to use an output parameter instead of returning a value as a resultset. This is a more efficient way to return a simple result as well.

|||

Thanks for the tips every one, they helped me greatly!