Best solution, iterate over millions records and call extended sp

Hi,
I need to iterate over millions rows in a table and I need call an extended
stored procedure (written in C++ and not possible be written in TSQL) using
the columns of each row as parameters and write the return values to an new
table. The current script open a cursor.
What's the best way to implement it? BCP to a text file and external program
parse and write the text file and BCP back? sp_cmdshell an executible for
each row? (so needn't worry about C++ memory leak issu). XML?....
Thanks,"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,
I'd say the "best" solution would be to rethink your architecture and
implement it differently if you plan to do this on a regular basis. This
doesn't sound like a very scalable or desirable way to use a client-server
database. Have you considered using SQL Server 2005, where you can implement
.NET code in the database? Or implementing your code in ADO rather than wit
h
an XP?
If it's just a one-off requirement then you just need to test which approach
works best for you. It isn't really a SQL question since, for the purposes
of this exercise, you are just using SQL Server as a file dump rather than
what it was designed for. Why not just loop in TSQL and call the proc for
each row?
David Portas
SQL Server MVP
--|||If you have a situation that calls for looping through a cursor, then it's
better to implement the cursor on the client side than on the server. Open a
read-only, forward only ADO recordset and Command.Execute the stored
procedure for each row.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,sql

Best solution, iterate over millions records and call extended

In fact, the functionarity needs to be available on the server. So I will
created a program, maybe C# or C++ program to do the looping and calculate
and put the executible on the server so it can be launched via xp_cmdshell..
.
It should be better than big TSQL cursor?
"JT" wrote:

> If you have a situation that calls for looping through a cursor, then it's
> better to implement the cursor on the client side than on the server. Open
a
> read-only, forward only ADO recordset and Command.Execute the stored
> procedure for each row.
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
>
>In fact, my question is
Transact-SQL script "declare cursor" (fast forward) vs. Client side ADO code
with fast forward server cursor
Which one is better for very large rows?
"nick" wrote:
> In fact, the functionarity needs to be available on the server. So I will
> created a program, maybe C# or C++ program to do the looping and calculate
> and put the executible on the server so it can be launched via xp_cmdshell
..
> It should be better than big TSQL cursor?
> "JT" wrote:
>

Best practice for writing own system procedures

Hello,

I'm searching for a best practice or other documentation for writing my own 'system procedure'.
I want to write procs which I can call in the context of every database without using a database name analogous to sp_who for example.
I read about the 'Resource Database'. All system procedures are stored in that readonly database and appear logically in the sys schema of every database.
But I couldn't find documentation about writing my own 'system procedure'.
I discover so far that procs with prefix sp_ stored in the master database do what I want. But is that the only way or is there a better way to do it?
In other threads I read that it is recommended not to use sp_ as prefix for procedures

Wolfgang?

It's not recommended to use the sp_ prefix because there is a slight performance hit if you use it for user stored procedures in a database other than master -- this is because SQL Server will look in master for the stored procedure, if it sees the sp_ prefix. However, if you're creating "system" stored procedures that should be callable from all databases, and which are created in master, then the sp_ prefix might make sense...

There are really no best practices I know of, that apply only to stored procedures in master. They follow the same basic rules as any other stored procedure. Note that you can't create objects in (or even access) the resource database -- it is hidden so that only the query engine can access it.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

|||Just in addition to Adam, the performance hit will be caused from the Cache Miss that is produced if the procedures takes the sp_ prefix, a schema lock on the procedure and a recompilation of the procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for your answers.
As only procs with prefix 'sp_' stored in master are callable from all other databases I assume that that is the right way to write my own "system" procedures.
But I'm not very happy with that approach. I don't like to store ny own procs and tables in the master database because master is a central database for the whole server.

Wolfgang|||

You would have to define "system stored procedure" first. Very particularly, a system stored procedure has a prefix of sp_, is written by Microsoft, shipped with the product, and has special rules for name resolution.

I believe you are asking about "administrative stored procedures", these are procedures that you write that perform actions you need against one or more databases within a SQL Server. For these, I use an administrative database on the instance. I usually call it admin. In that database, I put all of my administrative procs along with any supporting tables, views, functions, etc. Any procedure can be called from any database, you just have to fully qualify the procedure.

|||OK, then talk about "administrative stored procedures".
Unfortunatly your approach doesn't fit my requirements. When I execute in a database kunktest1 'admin.dbo.kunkproc' I'm in the context of database admin and not in the kontext of database kunktest1. But I need to be in the kontext of the database from where I call the "administrative stored procedure" to select the objects of that database for example.

Wolfgang|||Normally you will be able to access the tables in the other databases using the three part names of the objects, like DatabaseName.OwnerOrSchema.Objectname. If this is not feasible for you and you really need the context of the database I would suggest generating a procedure in each database customized for each database.

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

Best method of checking for duplicate entries in SQL Server

Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.

In my Favorites table, the 3 fields are....

favoriteId (set as primary key)
userId
carId

I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.

1) Check proactively by doing a....
SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)
If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.

2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.

Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?

TIA for your help.

Your best bet IMO would be a derivative of option #1.

If NOT Exists(select favoriteID from favorites where userid = @.x and carid = @.y)
BEGIN
--insert record here
return 0
END
ELSE
BEGIN
return 1
END

where a return of 1 means the record exists

|||

Thanks for the reply, Diamsorn. Is that something called a stored procedure? I'm not terribly familiar with them.... but if that's the only way to go, I'll try to research them. Is there a way to translate that into an inline sql query in my VB code? I've been using the SqlCommand object with an SqlDataReader to get at my queries. Is your method noticeably more efficient that mine? Here's my snippet of code where I proactively check for an existing entry, then I do one of two actions depending on whether or not the record exists. Thanks for helping out an ignorant.

Dim favoriteIdAsInteger
Dim favoriteLookupCmdAsNew SqlCommand _
("SELECT favoriteId FROM Favorites WHERE userId = " & userId &" AND carId = " & carId, dbConnection)
thisReader = favoriteLookupCmd.ExecuteReader()
While (thisReader.Read())
favoriteId = thisReader.GetValue(0).ToString
EndWhile
thisReader.Close()

If (String.IsNullOrEmpty(favoriteId) =False)Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) VALUES (" & userId &", " & carId &")", dbConnection)
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="Error in adding car."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf
EndIf

Thanks.

|||

Its not a stored procedure, but rather the T-SQL that will do the same as what you are doing above inside of a stored procedure.

Either method is fine, doing it in a single stored procedure reduces the number of trips back to the server your app has to make,

Also you reduce the chance for SQL injection attacks.
If your going to go with the method you have above, then your going to want to use parameters to also reduce the chance for SQL injection attacks.

 
Dim favoriteLookupCmdAs New SqlCommand _ ("SELECT favoriteId FROM Favorites WHERE userId = @.useriD AND carId = @.carID", dbConnection)favoriteLookupCmd.Parameters.AddWithValue("@.useriD", userID)favoriteLookupCmd.Parameters.AddWithValue("@.carID", carID)
 

and do the same for your 2nd sql statement as well.|||

INSERT INTO Favorates(UserID,CarID) SELECT @.UserID,@.CarID WHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)

because the check and insert are wrapped into a single SQL Statement, locks are automatically placed on any records it matches in the WHERE clause until the INSERT has completed. This guarantees that you will never insert records into the favorates table that already has the record. You could also do this:

IF NOT EXISTS(...) INSERT ...

but the locks (read/shared) on the records in the exists clause are released prior to the insert, which leaves an opportunity for a record to be inserted between them.

Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) SELECT @.userid,@.caridWHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)", dbConnection)

with insertFavoriteCmd

.Parameters.Add("@.userid",sqldbtype.nvarchar).value=userid

.Parameters.Add("@.carid",sqldbtype.integer).Value=carid

end with
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf

|||

How should i write update query for the same ?

plz help me out of this guys....

beginner.. :-)

Hello there

I am a very beginner..

I need to know if this is the write structure for doing this:

1. this func call for 4 other func: is it suppose to look like this?

2. with what i should replace the RETURNS int if its return date & int

thanks

ALTER FUNCTION [dbo].[StatisticsEx2_0Func]

(

@.Date_M_Y smalldatetime ,

@.FK nchar (20) ,

@.BizID int

)

RETURNS int

AS

BEGIN

DECLARE @.ResultVar int

set @.ResultVar = (SELECT

dbo.StatisticsEx_2_1_SingleMonthlySumFunc.A, dbo.Statistics_2_1_SingleAnnualAvgFunc.B, dbo.Statistics_2_1_AllGropsMonthlyTotalFunc.C, dbo.StatisticsEx_2_1_AllGroupsAnnualAvgFunc.D

FROM dbo.Statistics_2_1_AllGropsMonthlyTotalFunc, dbo.Statistics_2_1_SingleAnnualAvgFunc, dbo.StatisticsEx_2_1_AllGroupsAnnualAvgFunc, dbo.StatisticsEx_2_1_SingleMonthlySumFunc)

RETURN @.ResultVar

END

If you want to return more than one value then use Inline Table /Table Valued UDF.

If you use sql server 2005 then you can utilize the UDT (.NET Class) to return as Structure.

Create Function [dbo].[StatisticsEx2_0Func]()

Returns table

as

Return

(

Select 100 i, Cast('1/1/2007' as Datetime) d

)

go

Select * from [dbo].[StatisticsEx2_0Func]()

|||

amm..

the "mother" func suppose to get 3 parm – every child func use 2 param out of the 3

then let the table

so...?

Create Function [dbo].[StatisticsEx2_0Func]()

Returns table

as

Return

(

Select

@.Date_M_Y smalldatetime ,

@.BizID int ,

@.FK

)

go