Help on database choice?

Hi there,
I am currently writing an application for NNTP, the application requires
very FAST inserts, updates etc.
I started to write it with mySQL and I got good results, I was wondering if
anyone can confirm speed comparisions (and other comparisons) using MSDE
rather than mySQL.
As far as i know my app which runs on the desktop requires no special
licenses to use MSDE but mySQL does.
The application with only have 1 concurrent user and is being developed with
c# Visual Studio.net
Any help or information anybody can offer would be gratefully appreciated
Thanks in advance
Ian
Oh I forgot to mention, there is only 1 concurrent user but that user has
approximately 10 connections to the database. Maybe this will have an effect
on performance,.
THanks again
Ian
"Ian" <ianinmalaga@.hotmail.com> wrote in message
news:eLL8qdnZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I am currently writing an application for NNTP, the application requires
> very FAST inserts, updates etc.
> I started to write it with mySQL and I got good results, I was wondering
if
> anyone can confirm speed comparisions (and other comparisons) using MSDE
> rather than mySQL.
> As far as i know my app which runs on the desktop requires no special
> licenses to use MSDE but mySQL does.
> The application with only have 1 concurrent user and is being developed
with
> c# Visual Studio.net
> Any help or information anybody can offer would be gratefully appreciated
> Thanks in advance
> Ian
>
sql

help needed in error handling and undo transaction

I am reading a temptable, and doing 2 inserts. In case of error, i want the 2 inserts to be undone, and move to the next line. The complete opposite is happening and the process is being stopped while i wanr it to move on!Help appreciated!

This is my code:

BEGIN TRANSACTION

if exists(select [id] from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#textfile'))

drop table #textfile

CREATE TABLE #textfile (line varchar(8000))

BULK INSERT #textfile FROM 'c:\init_newsl.txt'

DECLARE table_cursor CURSOR FOR SELECT line FROM #textfile

OPEN table_cursor FETCH NEXT FROM table_cursor INTO @.oneline

SET XACT_ABORT ON

WHILE (@.@.FETCH_STATUS = 0 AND @.oneline != '')

BEGIN

INSERT INTO mytable1 values(@.f1, @.f2)

IF @.@.ERROR <> 0

BEGIN

PRINT 'Error in insertion of table1. Error is ' + LTRIM(STR(@.@.ERROR))

RAISERROR('',15,1)

goto next_line

END

INSERT INTO mytable2 values(@.f3, @.f4)

IF @.@.ERROR <> 0

BEGIN

PRINT 'Error in insertion of table2. Error is ' + LTRIM(STR(@.@.ERROR))

RAISERROR('',15,1)

goto next_line

END

goto next_line

next_line:

FETCH NEXT FROM table_cursor INTO @.oneline

END /* while fetch status = 0 */

Hi Terry,

You need to begin a transaction for each unit of work that you with to either commit or rollback. In your case, you are encapsulating the entire process in the transaction by placing your begin outside of the individual fetch statements. Also, I can't see a commit/rollback anywhere.

I would question your need to use a cursor here - can you post what you're trying to do and maybe we can help?

Anyway, if you did want to go down the cursor route, you would need to:

WHILE (@.@.FETCH_STATUS = 0 AND @.oneline != '')
BEGIN
BEGIN TRANSACTION t1

INSERT INTO MyTable1...

IF (@.@.ERROR <> 0)
BEGIN
ROLLBACK t1
GOTO NextLine
END

...etc

NextLine:
IF (@.@.TRANCOUNT >= 1) -- or >= 2 if you've a parent tran...
COMMIT t1

FETCH...
END

Cheers,
Rob

Help needed

Hi all,

I am trying to write a stored procedure, which has two insert statements.

the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.

Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?

thanks for your help!

Hello rasesh_dave,

Yes, you can do this with one stored procedure.

The newly inserted primary key value can be retrieved with @.@.Identity.

|||

Ofcourse, just put the two insert statements into one proc.

|||

yup, but how do i pass a list / array of items to be inserted into the second table?

|||

Post the code you currently have so we can see what you are doing..

|||

Okay,

I have two stored procs. The first one is

CREATE PROCEDURE [dbo].[sp_EventNew_Insert]

@.EventName VARCHAR(50),
@.StartDate DATETIME,
@.EndDate DATETIME,
@.Organiser VARCHAR(50),
@.Telephone VARCHAR(15),
@.Fax VARCHAR(15),
@.Email VARCHAR(50),
@.EventURL VARCHAR(50),
@.PendingVerification BIT,
@.SubmitersName VARCHAR(50),
@.SubmitersEmail VARCHAR(50),
@.EventVenue VARCHAR(50),
@.EventCity VARCHAR(50),
@.EventState VARCHAR(50),
@.CountryID VARCHAR(50),
@.SubmiterName VARCHAR(50),
@.SubmiterEmail VARCHAR(50)

AS

INSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,
Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)
VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax, @.Email, @.EventURL, @.EventURL, @.PendingVerification,
@.SubmiterName, @.SubmiterEmail)
SELECT @.@.Identity as EventID
GO

which returns the EventID which is received by the DataAdapter class, and

then executes a loop for say 10 SiteID and calls another stored procedure for each siteID which is

CREATE PROCEDURE [dbo].[sp_EventSitesNew_Insert]
@.EventID INT,
@.SiteID INT,
@.LiveOnWeb BIT
AS

INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)
VALUES (@.EventID, @.SiteID, @.LiveOnWeb)
GO

is there a way i can pass an array of siteID into the sp, and execute a loop without generating a loop at the Data Adapter?

|||

Here's how I would modify your proc

(1) DO NOT name your procs with "sp_..". sp_ means system proc and SQL Server looks for the proc under master db thinking its a system proc and if it doesnt find there, it will look under the db you are running queries from. So this is an unnecessary overhead.

(2) Create a function that takes a concatenated string and returns a table of parsed values. Refer:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

(3) Finally modify the proc as follows:

CREATE PROCEDURE [dbo].[SP_EVENTNEW_INSERT]@.EventNameVARCHAR(50),@.StartDateDATETIME,@.EndDateDATETIME,@.OrganiserVARCHAR(50),@.TelephoneVARCHAR(15),@.FaxVARCHAR(15),@.EmailVARCHAR(50),@.EventURLVARCHAR(50),@.PendingVerificationBIT,@.SubmitersNameVARCHAR(50),@.SubmitersEmailVARCHAR(50),@.EventVenueVARCHAR(50),@.EventCityVARCHAR(50),@.EventStateVARCHAR(50),@.CountryIDVARCHAR(50),@.SubmiterNameVARCHAR(50),@.SubmiterEmailVARCHAR(50),@.SiteIDVarchar(100),@.LiveOnWebBITASBEGINSET NOCOUNT ONDeclare @.EventIdINTINSERT INTO tblEventTest (EventName, CountryID, StartDate, EndDate, Venue, City, State, Organiser, Telephone, Fax,Email, EventWebsite, DisplayURL, PendingVerification, SubmitersName, SubmitersEmail)VALUES (@.EventName, @.CountryID, @.StartDate, @.EndDate, @.EventVenue, @.EventCity, @.EventState, @.Organiser, @.Telephone, @.Fax,@.Email, @.EventURL, @.EventURL, @.PendingVerification,@.SubmiterName, @.SubmiterEmail)SELECT @.EventId = SCOPE_IDENTITY()Declare @.SiteIdTableTable (SiteIdINT)INSERT into @.SiteIdTableSELECT *FROM dbo.fnGetIDs (@.SiteID)INSERT into tblEventSitesTest (EventID, SiteID, LiveOnWeb)SELECT @.EventID, SiteID, @.LiveOnWebFROM @.SiteIdTableSET NOCOUNT OFFEND

|||

Thanks for the code, i am trying to implement it but is gives me an error

Server: Msg 208, Level 16, State 1, Procedure sp_EventNew_Insert, Line 35
Invalid object name 'dbo.fnGetIDs'.

I am trying this in query analyser! i dont know if i neet to add any udf as well with that?

|||

You need to create the function based on the code I provided in the article link.

|||

Thanks Dinakar,

I wonder how much efficient code a developer can write, if they know the system copletely!

|||

rasesh_dave:

Thanks Dinakar,

I wonder how much efficient code a developer can write, if they know the system copletely!

Definetely...