High volume batch insert

Hi,
I need a SQL program to insert a record into a parent table and then up
to 200 child records, using the primary key of the parent record. Whats
the best way to structure this procedure for performance and
reliability ? e.g
INSERT INTO tblExam ( dteExam,intModule,) Values () ...
SET intExamID= @.@.IDENTITY
' Psedo-code
For each Candidate in frmExamRequest
INSERT INTO tblCandidateExam ( intExamID, intCandidateID )
Next
The data is all coming from a form on an ASP page, I could break it
into two forms but for the users a single form with exam date, and list
of candidates they can check/uncheck is much easier.
If I have 2 stored procs to insert a exam instance & candidate
instances, the latter proc will be called 200 times over an ADODB
Connection. Is it possible to have a single stored proc to do the whole
task in a transaction with unknown number of candidates ?
thanks.When the user hits "Enter" you could either collect all the candidate IDs an
d
put them in a comma delimited string and then pass that string to the stored
proc that's doing the insert.
You could perform inserts into tblCandidateExam by looping through the
candidate IDs.
I think that's probably the easy way. Hope that's helpful
Cheers,
mmm
"cc900630@.ntu.ac.uk" wrote:

> Hi,
> I need a SQL program to insert a record into a parent table and then up
> to 200 child records, using the primary key of the parent record. Whats
> the best way to structure this procedure for performance and
> reliability ? e.g
>
> INSERT INTO tblExam ( dteExam,intModule,) Values () ...
> SET intExamID= @.@.IDENTITY
> ' Psedo-code
> For each Candidate in frmExamRequest
> INSERT INTO tblCandidateExam ( intExamID, intCandidateID )
> Next
> The data is all coming from a form on an ASP page, I could break it
> into two forms but for the users a single form with exam date, and list
> of candidates they can check/uncheck is much easier.
> If I have 2 stored procs to insert a exam instance & candidate
> instances, the latter proc will be called 200 times over an ADODB
> Connection. Is it possible to have a single stored proc to do the whole
> task in a transaction with unknown number of candidates ?
> thanks.
>|||See if one of these helps:
http://www.aspfaq.com/show.asp?id=2248 Arrays & Lists
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
http://www.users.drew.edu/skass/sql...bleProc.sql.txt List to
Table script
Andrew J. Kelly SQL MVP
<cc900630@.ntu.ac.uk> wrote in message
news:1117472922.543985.50420@.g14g2000cwa.googlegroups.com...
> Hi,
> I need a SQL program to insert a record into a parent table and then up
> to 200 child records, using the primary key of the parent record. Whats
> the best way to structure this procedure for performance and
> reliability ? e.g
>
> INSERT INTO tblExam ( dteExam,intModule,) Values () ...
> SET intExamID= @.@.IDENTITY
> ' Psedo-code
> For each Candidate in frmExamRequest
> INSERT INTO tblCandidateExam ( intExamID, intCandidateID )
> Next
> The data is all coming from a form on an ASP page, I could break it
> into two forms but for the users a single form with exam date, and list
> of candidates they can check/uncheck is much easier.
> If I have 2 stored procs to insert a exam instance & candidate
> instances, the latter proc will be called 200 times over an ADODB
> Connection. Is it possible to have a single stored proc to do the whole
> task in a transaction with unknown number of candidates ?
> thanks.
>

High Cpu Usage During Logon

MY PROBLEM IS SOME HOW STRANGE TO ME AS I AM NOT AN EXPERT...

WE USE AN ERP PROGRAM ON A SQL7 DATABASE....WHEN USERS LOGON AND TRY TO WORK I HAVE 100% CPU USAGE AND THE SERVER IS NOT RESPONDING UNTIL I KILL THE APP. IF THE USER HAS ADMINISTRATOR RIGHTS EVERYTHING WORKS SMOOTHLY...ADMINISTRATOR RIGHTS FOR THE DATABASE...

WHAT I MANAGED TO FINDOUT IS THAT WHAT IS RUNNING DURING THE SERVER LOCKS IS SP_CURSOROPEN

ANY SUGESTIONS?.....Howdy

Sounds like you have possible wrong level of access for the app - try adding it to the db_datareader & db_datawriter database roles & see what happens. If that doesn't make any difference, remove from all roles ( except public ) then only to db_owner database role & see what happens.

Cheers,

SG.|||nothing.......

some new facts on the matter are that the users eventualy log on and can work but the speed is very slow when they try to change menu options.....the problem occured after 3 years of smoothly operation and that is the strang thing....also when i say admin right i mean from the apps option since on the DB i have only one user (SA) who is used by the app......|||Howdy

Well, sa will give you all the access you need.

How are you for disk space - especially for the TEMPDB database & the production db? Also, sounds possible you may be paging memory madly out to disk , hence the slow speed. Have you run perfmon to check the efficiency of the server?

Sounds odd that after 3 years of smooth operation all goes to custard....

What else has changed recently? Any service pack updates etc?

Cheers,

SG|||nothing except a shrink and a reorganize.....the second was running on a schedule for some time now......
as for space everything is ok....
performance has nothing to do also i believe it is something else....something about the app... since the only user that logs on sql is sa nomatter if i give him rights using the app or not....|||Howdy

Common sense tells me SOMETHING has to have changed. If you havent run out of disk space, the server isnt under powered then someone, somewhere has to have altered something.

Try running Profiler and see what activity is occurring in the database just as it starts to slow down. Is there a particular sequence of events that happen as this problem starts, or is it an ongoing problem? Has the number of users gone up? have new functionality been added to the system?

It wouldnt be the first time someone has altered a system but not told anyone....

Cheers,

SG|||at first i would like to say thanks for your help....

i managed to fix the problem but i dont know yet what coused it....
the solution was to reindex the tables that are involve to security for the app and manualy updatestats for the production db.....
know i have a small delay which is acceptable and everybody is working ok...

thanks again...|||Howdy,

....well without sounding a little comical...that would have been one of my next suggestions......

Check the settings for the database - they should be ( by default ) set to update stats automatically. Its a bit scary that this was turned off...You can create a separate Maintenance Plan to do this - we do and sample 25% of the database. Also a reindex of each index in the database is useful - we do it weekly & it keeps the index fragmentation right down and speed up.

You can also turn off autoshrink for the databse which will lift the speed a bit more but you will need to schedule a job to do this at least weekly as this will keep the databases from getting too big & slowing down.

Also, turn off the autoclose for the database - this will remove a small overhead on performance.

Cheers,

SG.

Hierarchy MDX

Below is my hierarchy :

Program 1

Supplier 1

Supplier 2

Supplier 3

.

.

.

Supplier N

I need an MDX expression which on retrieves Suppliers for Program 1 and so on.

So right now when i browse my cube I see all Suppliers for all Programs because of which there are alot of blank rows.

I need only Suppliers unique to each Program. So the final outcome of the hierarch above should be :

Program 1

Supplier 1

Supplier 5

Supplier 7

since Program 1 has only 3 suppliers.

TIA

- Russ

Not really all that clear on the structure of your dimension. Take a look at CROSSSJOIN, NONEMPTYCROSSJOIN, NONEMPTY, EXISTS, and the NON EMPTY keyword.

B.

|||

Bryan,

Thanks for the pointers. I'll look into the Keywords that you mentioned.

However, here is my situation in a little more detail.... This is what it looks like when i browse my cube :

- Program 1

- Supplier 1

.

.

.

.

- Supplier 10

- Program 2

Supplier 1

.

.

.

.

Supplier 10

and so on..."ALL" the 10 suppliers are showing up for each program. However , Program 1 has only 3 suppliers and Program 2 has 5 suppliers. I need an MDX which can be applied as a formula at the Program level and applies to all its supplier children displaying only the ones which exist with values and not all 10 suppliers.

I could do this manuall by removing each Supplier which doesnt exist for that Program but i need a generic MDX which looks at the Program Number and applies it to its children at all levels.

Any ideas ?

Thanks

Russzee

|||

Hi russzee,

how the Programs and Suppliers are related in your cube desing? Only in fact table? Do you use any of wide spreaded SSAS Client like Excel or Proclarity or you write MDX self?

|||

Russzee,

Vladimir is trying to clarify the structure of your dimensions and cube. What is not clear is whether Programs and Suppliers exist within a single dimension or are part of two separate dimensions associated with your cube. Either one of these situations can give you a structure such as you identified.

B.

|||

Hello,

Thanks once again for the follow up..

Program and Suppliers are two seperate dimensions in SSAS 2005 and there is a Fact Table called SupplierProgram linked in a Star Schema as shown below :

Program <- SupplierProgram > Supplier

So I have dynamic Supplier row members for each Program and i need an MDX which can identify the 'Supplier' row members on the fly from their parent 'Program' Members.

My client application is Performance Point Dashboard Designer which has no MDX support in CTP_2 so I'm looking to create this MDX in SSAS 2005 and port it to the Performance Point Designer.

Thanks

russzee

|||

Here are a few queries that might get you headed in the right direction. Within a dimension, SSAS employs auto-exists functionality for this kind of thing. Across dimensions, you have to leverage a fact table to determine what represents a valid combination. These results may all look the same (except for the baseline query that demonstrates a full cross product). Still, there are subtle differences between these you need to understand before taking one path or the other.

Good luck,
Bryan

Code Snippet

select -- BASELINE QUERY

[Measures].[Reseller Sales Amount] on 0,

Product.Category.Category.Members * Employee.Employee.Employee.Members on 1

from [Adventure Works]

;

select

[Measures].[Reseller Sales Amount] on 0,

NON EMPTY Product.Category.Category.Members * Employee.Employee.Employee.Members on 1

from [Adventure Works]

;

select

{} on 0,

NONEMPTYCROSSJOIN(Product.Category.Category.Members,Employee.Employee.Employee.Members) on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

;

select

{} on 0,

Product.Category.Category.Members *

NONEMPTY(Employee.Employee.Employee.Members,Product.Category.Category.Members)

on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

;

select

{} on 0,

Product.Category.Category.Members *

EXISTS(Employee.Employee.Employee.Members,Product.Category.Category.Members,'Reseller Sales')

on 1

from [Adventure Works]

;