MSSQL2K SP related question

McGintyMcGoo

I am currently developing various reports for a system I am working on. We have used Access to develop the frontend to an MSSQL backend database.

Some reports are directly bound to queries on the backend.

Other, parameterised reports are bound to storedprocs. The storedprocs themselves are just SQL statements with some dynamic parameters.

Now I've gotten to some really complicated reports which require a lot of data manipulation from various tables. The client PCs are too low-spec to create recordsets etc on the client, so I am manipulating the data on the backend. The storedproc I have created uses a lot of loops etc, but the final output is to a temp table. To bind this to a report, I was going to write the result set to a permanent table on the backend. Is this bad practice?? I see it as being the same as a report being bound to a query. The difference here though is the introduction of data redundancy. This results table would be emptied and then refilled each time the report was run.

Any opinions or suggestions would be appreciated!

Morpheus

So you say that the permanent table is being used as a recordset?
I assume it is being written to, read from and deleted all in the same process?
This process is all run in producing the report?

Ok, well would there be any problems if more than one user tries to run the report simultaneously?

We had that problem here so to get around it we had to introduce UserIDs so that records would be written to the table and deleted from it for a certain users ID . Means that you can have simmultaneous access read/write and dont require lock out accessibility etc.

McGintyMcGoo

Cheers Morphéus

I had considered the simultaneous access problem, so I had planned on......
When a user runs the report, I empty the table right away and write one temp record saying "please try again blah blah blah". The table would be populated at the end of the storedproc so the chances of a clash in that split second are very remote (but possible).

Anyway, I like the idea of resolving the issue by also using the userid. This would stop potential clashes. Would you leave the users resultset in that table all of the time, or would you delete the data when they exited the reporting section? Obviously you would regenerate the resultset when they run the report.

Thanks for the help.

Morpheus

Glad I could help, heres some sample code of how I approached the problem of writing to and cleangin up the permanent table...

First we always delete any records on the table that may exist for that user from the last time they ran the process.


DELETE MyPermanentTable WHERE MyPermanentTable.UserKey = (SELECT dbo.GetAMKeyFrmLogin(SUSER_SNAME()));


The we insert the records for a specific user....


INSERT INTO MyPermanentTable ( whatever, fields, you ,want, UserKey )

SELECT whatever, fields, you, want, (SELECT UserKey FROM UserTbl
WHERE UserKey = ( SELECT dbo.GetAMKeyFrmLogin( SUSER_SNAME()) ) ) as UserKey

FROM MainDataTable WHERE (whatever the selection criteria is);




heres the code for the User Defined Function GetAMKeyFrmLogin() used in the above 2 statements...


/* Object: User Defined Function dbo.GetAMKeyFrmLogin */
CREATE FUNCTION [dbo].[GetAMKeyFrmLogin] (@LoginAcc AS nvarchar(50))
RETURNS int AS
BEGIN
RETURN (SELECT Usertbl.UserKey FROM UserTbl WHERE UserTbl.LoginAcc = @LoginAcc)
END


Then u carry on your process, selecting the records from the table (remember to select them for the individual user aswell) and printing these records in your report.

Then the next time this user runs it, it will delete these records that this user just processed and start inserting their new records.

It means that each user will only have one reports worth of data in the table at any time and they will never conflict with each other if writing to or reading from it at the same time.

Hope this helps?

McGintyMcGoo

Morphéus

Thanks again for the help, but I have to ask .....

If you ended up having 20 different reports that returned completely different fields and you had a userbase of 100 users, would you still do it the same way?
I ask this because, although your solution is perfect for me now, it still means that I will have a temporary table with 50,000 records (500 records average X 100 users) to cover one report.

Introducing another report could increase this to 100,000 records and so on ....

I'm just throwing this out there for conversation!

Morpheus

Im not too sure, i havent worked on anything that big as yet...

Can you not just write the stored procedure out directly to the permanent table getting the users ID using the same method above and remove the temp table completely, thats what we did, we didnt want temp tables at all.

Also you are only going to be holding a reports most recent recrodset in the table for individual users, at any one time. IIm sure, that i you need you can always delete the records for a user when theyve finished processing the report???

McGintyMcGoo

Apologies for that! When I said Temporary table, I was actually referring to the Permanent table that contains temporary results.

I do plan on deleting the data for each user when they are finished!

Thanks again!

bonkey

Lads...

as a suggestion for when using a permanent table in this way, and in a multi-user environment :

Add a column to the permanent table called UserId.
@spid.

SPID is the System Process ID - it is unique to every connection@SPID will be different for each of them - a problem which Morpheus' code could fall prey to. Also, its less work, IMHO.

This may not work if you have a coding model which may use different/multiple connections...but if you're using the same connection to run the stored procedure and then query the data back from the database, you should be laughing.

I do plan on deleting the data for each user when they are finished!

Good idea, but you also need to consider what should be done when a user's connection terminates abnormally - which will mean that there is no way of them issuing a delete statement.

@SPID approach above, the easiest "clean-up" technique is to write a stored procedure which deletes all records in the report table which have an SPID not currently associated with the logged-in processes. Then schedule that to run at a period that suits you. If you do a "normal" clean-up (i.e. deleting after the report is generated), then this is just an extra step which will cater for failures...so running it nightly/weekly would be enough.

As for potentially having 100,000 records in the table. Who cares!!! Thats still a relatively small amount of data If you have your indexes set up properly, that won't cause the slightest problem.

McGintyMcGoo

Thanks Bonkey! The application captures the users id anyway and it is stored as a global variable throughout their user session. This is the value I would write to the table.

In the case of the user's connection terminating abnormally, I would put a DELETE at the start of the stored proc to remove their last resultset anyway!!

Another point that I'd like your opinion on is ...
If you have 20 of these reports, all completely different (i.e. different fields and datatypes returned), would you consider it bad programming or database design to physically create 20 new tables just to accomodate those reports??

Morpheus

Good suggestions bonkey, i think ill review my design now!

bonkey

Originally posted by McGintyMcGoo
Thanks Bonkey! The application captures the users id anyway and it is stored as a global variable throughout their user session. This is the value I would write to the table.

Like I said...I prefer the SPID approach because its unique per connection. If two people log in using the same username, then they can conflict with each other. Its unlikely, I know....but anyways.

In the case of the user's connection terminating abnormally, I would put a DELETE at the start of the stored proc to remove their last resultset anyway!!

Yup. I'd have something like that as well.

The thing is that with the SPID approach, when the user logs out and logs back in, they will have a different SPID, cause its a different connection. This is why you need the "independant" cleanup job.

If you don't use the SPID, but go for a username, then you're dead right - having the delete at the start of the SP should be enough.

Another point that I'd like your opinion on is ...
If you have 20 of these reports, all completely different (i.e. different fields and datatypes returned), would you consider it bad programming or database design to physically create 20 new tables just to accomodate those reports??

Hmmm. Difficult one to answer. In general, I try and avoid these "permie temp tables", and only use them when there is a convincing reason to. Normally, I just have a SELECT at the end of my stored proc, and bind the report fields to the output fields of the SP, and thus avoid all the hassle.

In a complicated scenario (like what you described in the first post), I would generally favour doing everything inside the stored proc as follows :

CREATE STORED PROCEDURE sample AS

-- step 1 : create the temp table 
  CREATE TABLE #sampleTemp (
    outputfield1  INT,
    outputfield2  VARCHAR(255),
    ...
  )
  
-- step 2 : do all the horrid SQL To populate with complex, manipulated data  

  INSERT INTO #sampleTemp
    SELECT blah blah

  UPDATE #sampleTemp
    SET turnip turnip

  DELETE #sampleTemp
    WHERE daffodil daffodil
  
  ...
  
-- step 3 : select all the data back from the temp table : 

  SELECT ...
    FROM #sampleTemp

END

OK - you'll have to excuse any syntax errors - I haven't written MSSQL stored procs in a wee while, and am too lazy to refresh my memory on teh specifics, but you should see the idea.

Anyway, you can see the idea. This leaves everything nicely self-contained in the stored procedure.

The main drawback is that the temptable has no indexes etc. and so, if you need to do some *serious* manipulation of data, its probably more efficient to have a permie table ready and waiting, rather than explicitly creating indexes on the temp table. Its certainly a lot easier.

Having said all that, there's nothing wrong with creating specific report-tables like you want to. I just prefer the approach I just outlined. I wouldn't say that having X dedicated report tables is necessarily a bad design at all, as long as there was a need for them - i.e. you didn't decide to generate every report this way, including the trivial ones.

Just make sure they are clearly named (perhaps as rpt_<whatever> or something), and off you go.

Oh - if you stick with the User-ID instead of the SPID, consider allowing the user to call the thing without regenerating all the data - so that it just re-outputs the last report. Useful on seriously-processor-intensive reports.

Anyway...there's some thoughts for you.

jc

McGintyMcGoo

I'm blue in the face saying Thankyou, but ......... Thankyou!

I didn't realise that if I threw in a "SELECT" statement at the end of my stored proc code, that this would be the resultset that's returned to the client!! Great stuff altogether!!

However, on this occasion I'll stick with the temp_perm_table because the users will run a lot of filters and various sorts on the data, and there's no point in calling the stored proc each time!!!

Morphéus and Bonkey, thanks for your time!!! If you're both ever stuck in the middle of the Sahara Desert, give me a shout!

Morpheus

eh... okay... why?? are you working there or something crazy like that?

McGintyMcGoo

No, not at all ...... its just that I like plastic cups!!!! :ninja:

(let it go man, let it go!)

amen

one point about the temp able in a stored procedure
you can add index/keys to the temp table when creating it within the sp
not the most efficent but it can be done