Database

14-Sep-04 8:29

I am trying to write a small app to map out sql server logins that are no longer mapped to any databases. simple enuff. got the algorithm worked out. however having problem with ado.net returning varbinary value to compare against.

First i create a reader (a) with the following query: "select name,sid from master.dbo.syslogins"

Then I create a reader (b) with the following query: "select name from master.dbo.sysdatabases"

Everything okay so far.

Now I want to loop through each login and check to see if the sid is mapped to any user in the databases.

So I create a query as such:
cString = select count(*) from " + b["name"].tostring() + ".dbo.sysusers where sid = " + a["sid"].tostring();

then next statement is to setup a sqlcommand and executeacaler the cString query.

I go through with the debugger and check to see what the value of cString becomes and everytime it is translated to:
"select count(*) from Broadband.dbo.sysusers where sid = System.Byte[]"

this generates a sql exception error. I can put single quotes around it but it still looks like:
"select count(*) from Broadband.dbo.sysusers where sid = 'System.Byte[]'"

this does not generate a sql exception but neither does it work. no sid values equals the string System.Byte[]

Need help. Confused | :confused:

cpritt

Jon Rista14-Sep-04 8:44

14-Sep-04 8:44

If the variable 'a' is a SqlDataReader (or any data reader, for that matter), you have a whole slough of methods to convert the data. Try a.GetString(1) to get a string value from a["sid"], or try a.GetInt32(1) to get an int value.

cpritt14-Sep-04 9:52

14-Sep-04 9:52

variable "a" is a SqlDataReader. I have tried the other options like a.GetSqlBinary(1) and everything still is being interpreted as System.Byte[].

what i mean is that the c# text:
cString = "Select whatever where sid = " + a.GetSqlBinary(1)

is sent to sql server as "Select whatever where sid = System.Byte[]"

whereas a SqlException is generated.

what i want is:
"Select whatever where sid = 0x27FA32BE455789"

not sure how to get this

Jon Rista14-Sep-04 13:17

14-Sep-04 13:17

"SELECT " + whatever + " WHERE sid=0x" + a.GetString(1);

When you build a query to execute with a SqlCommand, its just a string. Don't call GetSqlBinary, because its going to return the native type for binary data: byte[]. Call GetString....the SqlDataReader should properly convert from the native Sql datatype to a string, and then you just append that string to the rest of the query.

you'll end up with a SELECT statement thats something like:

"SELECT col1, col2, col3 FROM table WHERE sid=0x27FA32BE455789"

If that select statement executes fine if you run it directly on the database, it should run fine through a SqlCommand. Its just a matter of retrieving the data from the resultset in the way you want it. Just because something is an int in the resultset doesn't mean you have to call GetInt().

cpritt14-Sep-04 18:57

14-Sep-04 18:57

Thanks for the input and patience to respond to my questions. I tried the GetString(1) and it generated a exception for illegal typecast. Not sure where to go from here. Just in case we are missing each other in the interpretation of what i am trying to do in the code, here is the code in question. Pretty short and simple. I have walked through this step by step and where known sid's are in the sysusers table the executescalar count is still 0 when it should be 1. Of course that could be where my understanding is causing me to blieve the code is not working:

private void GetLoginsNotMapped()
{
int count;
bool sidFound;
string user;
SqlTransaction my_SqlTransaction;
SqlCommand my_Command;
SqlDataReader my_Reader;
SqlConnection my_Connection;
SqlCommand m_Command;
SqlDataReader m_Reader;
SqlConnection m_Connect;
SqlCommand my_SqlCommand;
SqlCommand m_SqlCommand = new SqlCommand();
SqlConnection t_Connection;

try
{
m_Connect = ConnectToServer();
my_Connection = ConnectToServer();
t_Connection = ConnectToServer();

if (sqlSuccess == true)
{
cString = "Select name, sid from master.dbo.syslogins";
my_Command = new SqlCommand(cString,my_Connection);
my_Reader = my_Command.ExecuteReader();

m_SqlCommand.Connection = t_Connection;

while (my_Reader.Read())
{
sidFound = false;
user = my_Reader["name"].ToString(); //Used only for troublshooting

cString = "Select name from master.dbo.sysdatabases";
m_Command = new SqlCommand(cString,m_Connect);
m_Reader = m_Command.ExecuteReader();

while (m_Reader.Read())
{
cString = "Select count(*) from " + m_Reader["name"].ToString() + ".dbo.sysusers ";
cString = cString + "where sid = " + my_Reader.GetString(1) + "";

m_SqlCommand.CommandText = cString;

try
{
count = (int) m_SqlCommand.ExecuteScalar();
if (count > 0 ) sidFound = true;
}
catch (SqlException e)
{
logErrorMessage(serverName,e.Message);
}
}
m_Reader.Close();
m_Command.Dispose();
if (sidFound == false)
{
try
{
my_SqlTransaction = mySqlConnection.BeginTransaction();
my_SqlCommand = mySqlConnection.CreateCommand();
my_SqlCommand.Transaction = my_SqlTransaction;
my_SqlCommand.CommandText = "Insert into tblNotMapped (" +
"serverName, loginName) Values ('" + serverName +
"', '" + my_Reader["name"].ToString() + "') ";
my_SqlCommand.ExecuteNonQuery();
my_SqlTransaction.Commit();
my_SqlTransaction.Dispose();
}
catch (SqlException ex)
{
logErrorMessage(serverName,ex.Message);
}
}
}
my_Reader.Close();
my_Command.Dispose();
m_SqlCommand.Dispose();
}
}
catch (SqlException err)
{
logErrorMessage(serverName,err.Message);
}
}

Thanks ahead of time for any tips can be sent my way.

/cp

Jon Rista15-Sep-04 7:16

15-Sep-04 7:16

Well, lets see what we can do here. It seems its not possible to have ADO.NET automatically cast a varbinary to a string. So, what we need to do is find a way to manually cast it ourselvs.

I think there are several ways of doing this. So, after a few minutes research, here are the best options I came up with.

Try using my_Reader.GetSqlBinary(). From this SqlBinary object, call sqlBinary.ToString() to convert the value to a string. I'm not certain if it will convert byte by byte or if it will just do something similar to calling Encoding.ASCII.GetString(byte[]).

If calling ToString doesn't work right, you could try calling sqlBinary.ToSqlGuid(), then call sqlGuid.ToString(). You should only do this if the binary data being returned is 128 bits, or 16 bytes, long.

If the data is not 16 bytes long, and calling GetSqlBinary doesn't result in useful data, I'd manually build a string from your byte array. Something like:

byte[] binary = my_Reader.GetSqlBinary().Value;
string binStr = "";
for (int i=0; i

cpritt15-Sep-04 8:54

15-Sep-04 8:54

Whooo Hwoooooo!!!!!!!!!!

You duh MAN!!!!!!!!

that worked. I used the byte[] array and looped through each byte in the array.

I'm rockin and rollin now.

thanks a whole lot. saved my butt and allowed me to complete a project on time.

Jon Rista30-Sep-04 18:52

Extreme performance issues (SQL Server 2000/ADO.NET/C#)

30-Sep-04 18:52

I'm glad I could be of service. Smile | :)

Jon Rista14-Sep-04 8:14

Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)

14-Sep-04 8:14

I'm using ADO.NET in a windows service application to perform a process on
SQL Server 2000. This process runs very quickly if run through Query
Analyser or Enterprise Manager, but takes an excessively long time when run
through my application. To be more precise, executing stored procedures and
views through Query Analyser take between 10 and 20 seconds to complete. The
same exact stored procedures and views, run in the same exact order, through
my program, take anywhere from 30 minutes to 2 hours to complete, and the
system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
of processing power). I am at a complete loss as to why such a vast
difference in execution time would occurr, but here are some details.

The windows service executes on a workstation.
SQL Server 2000 executes on a server different from the workstation through
a 100mbps ethernet network.
Query Analyser/Enterprise Manager run on the same workstation as the windows
service.

The process is as follows:

1) Run a stored procedure to clear temp tables.
2) Import raw text data into a SQL Server table (Reconciliation).
3) Import data from a Microsoft Access database into 3 SQL Server tables
(Accounts, HistoricalPayments, CurrentPayments).
(This takes about 10 - 15minutes to import 70,000 - 100,000 records from
an access database, housed on a network share on a different server.)
4) "Bucketize" the imported data. This process gathers data from the 4
tables stated so far (Reconciliation, Accounts, HistoricalPayments,
CurrentPayments, and places records into another table (Buckets) and
assigned a primary category number to each record through a stored
procedure.
5) Sort buckets of data into subcategories, updating each record in
(Buckets) and assigning a sub category number, through another stored
procedure.
6) Retrieve a summary of the data in (Buckets) (this summary is a count of
rows and summation of monetary values), grouped by the primary category
number. This is a view.
7) Retrieve a summary of the data in (Buckets), grouped by both the primary
and sub category numbers. This is a view.

When I execute these steps manually through query analyser, (save step 3),
each query takes anywhere from 1 second to 20 seconds. The views,
surprisingly, take more time than the fairly complex stored procedures of
step 4 and 5.

When I execute these steps automatically using my windows service (written
in .NET, C#, using ADO.NET), the simple stored procedures like clearing
tables and whatnot execute quickly, but the stored procedures and views from
steps 4-7 take an extremely long time. The stored procedures take at a
minimum 30 minutes to complete, and sometimes nearly an hour. The views are
the worst of all, taking no less than 1 hour to run, and often two hours
(probably longer, actually, since my CommandTimeout is set to 7200 seconds,
or two hours). I have never seen such a drastic difference between the
execution of a query or stored procedure between query analyser and an
application. There should be little or no difference at all, considering
that everything is stored procedures (even the views...I wrap all the views
in a simple stored procedure that calls the view using a SELECT), and as
such executes on the server. Not only that, but Query Analyser is running on
the same exact box that the application is running on, and is connecting ot
the same SQL Server.

I doubt this is a network bandwidth issue, as after calling the stored
procedure from code, there is no network activity except mssql keep-alive
messages, until the procedure completes and returns its result set or return
value (if any), and then its only a momentary blip as the data is sent
accross.

I've followed proper practice when using views and stored procedures. When I
select, I always explicitly name the columns I wish to retrieve. I have
appropriate indexes on the columns in the 4 data tables. The queries that
execute in the stored procedures are fairly complex, involving summations,
count(), group by, and order by. I can understand a moderate difference in
performance between query analyser and an ADO.NET application due to
ADO.NETs extra overhead, but a difference between 20 seconds and 1 hour is
more than can be attributed to .NET overhead.

I greatly appreciate anyone who might have some insight to this offering
some help. I've scanned the net looking for similar situations, but
searching for them is somewhat difficult, considering the nature and volume
of factors. Thanks.

-- Jon Rista

Steven Campbell14-Sep-04 9:34

Steven Campbell

14-Sep-04 9:34

Some thoughts/questions, in random order:
1) Are you execuring the stored procedures synchronousy?
2) Are you using ADO.NET transactions? (don't)
3) Is your service running at a low priority (use task manager to view base priority)
4) Have you installed the latest service pack for whatever version of .NET you are using?
5) What network library are you using to connect?
6) In the SQL Server Client Network Utility, is the "Enable Shared Memory Protocol" checkbox checked? (Try unchecking it)
7) Which method are you using to execute (best would be ExecuteNonQuery)
8) Try placing SET NOCOUNT ON as the first line of your stored procedures

See also this MSDN checklist.

my blog

Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)

Steven Campbell14-Sep-04 9:35

Steven Campbell

14-Sep-04 9:35

(8) is probably your best bet (SET NOCOUNT ON)

my blog

Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)

Jon Rista14-Sep-04 13:13

Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)

14-Sep-04 13:13

Thanks for the questions. First, let me answer them:

1) I'm not sure what you mean by "synchronously". I just call sqlCommand.ExecuteNonQuery() after setting up the parameters.

2) Yes, I was. I eliminated them after adding a temp sp that I ran from Query Analyser. I added a SQL transaction around calls to all my other stored procedures, and processing time jumped a ton. I let it run for just over a half hour then killed it. I think transactions may have been the problem, or at least a large part of it. I don't know why, but my queries, which are few but complex, may just be too much to transact.

3) SQL Server is running at a normal priority (priority level 7).

4) All of .NET is fully updated to the latest. We actually have a few versions, but the code has a .exe.config specifying which version to use.

5) I'm connecting to SQL Server using TCP/IP.

6) Shared memory protocol is disabled.

7) I'm executing with ExecuteNonQuery().

8) I have not been using SET NOCOUNT ON. I forgot all about that, but I'll try adding it in a moment and see what happens.

Now, to my questions. What exactly happens when you run queries through a transaction? Do the changes made get marked in a certain way, or is there some more extensive processing done to properly transact a set of queries/procedure calls? When I ran each of these stord procedures one by one, manually in QA, each one took between 1 and 20 seconds to run. The whole process couldn't have even taken a minute, even including the time for me to type the commands. Is it really possible that enabling transactions could induce a performance hit of at least 240%, possibly more? What kind of difference is there in performance between using true SQL Server transactions, and ADO.NET SqlTransaction?

I've used transactions, and ADO.NET SqlTransactions, in the past. I've never run into such performance issues, but then again, I've never transacted a set of queries as complex as these. There arn't many of them, maybe 20 total, but they are all fairly complicated queries with lots of summations, a couple subqueries, and an average of 3 joins per query (except for DELETE's). Maybe some resources on SQL Server transactions, tips on improving their performance, proper ordering of queries and proper query structuring to maximize performance might help. I've been searching the net, but havn't found anything truely helpful yet.

Thanks for the tips so far. Wink | ;)

Steven Campbell14-Sep-04 14:08

Steven Campbell

14-Sep-04 14:08

Usually, if anything, transactions will speed things up. Everything in SQL is a transaction, so it is only the size that differs when you make "large" transactions.

The reason I asked about ADO.NET transactions, is that sometimes it is best to just stick to one type. So, if you are using SQL transactions, then just use that. If you are using ADO.NET transactions, then try and avoid SQL transactions.

Deadlocks can also be a problem when using transactions. Usually though, these will come back as errors. While your queries are running, you can run the command sp_who2 in Query analyzer, to see if any queries are being blocked. This can either be a deadlock situation, or just some other process that is also trying to do stuff.

I don't know how much it would help in this scenario, but for optimizing queries, my best tip is to use the "optimizer hints". For example, DELETE MyTable (TABLOCKX) WHERE X = 1. The TABLOCKX hint tells the query to make a single exclusive lock on the table, rather than using the default page-style locking. In certain queries, this can improve performance tremendously. (It applies to all types of queries, not just deletes).

my blog

Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)

Jon Rista14-Sep-04 14:26