Mutli User and data build for reports for each user.

Question

Mutli User and data build for reports for each user.

Digs

SSCertifiable

Points: 7333
More actions
January 29, 2005 at 1:39 pm

#163522

Hi,
I will have users requesting the same report at the same time but with different WHERE clauses.
I under stand using the TempDB will provide the specific needs of each user. With the created objects destroyed when users logg off.
I am trying to learn best practices to set up data builds to meet a multi user environment. If there is any articles on it please advise.
My question..

Say I have a MAIN database thats holds base data ( Say transactions by Customer, records 1,000,000) and and grouping tables ( Say Customer Details, records 50).
I wish to use the tempdb in a SQL server to build report for the single user logged on ( knowing that these objects will be destoryed when user loggs off), also allowing many users to select the same report at the same time.
Question
I know I can CREATE tables in Tempdb and INSERT specfic data selected ( data selected on date range say) from the base data and manipulate as I wish, but when finished with this manipulation, I may wish to group data up based on grouping tables ( using JOINS ) for the final presentation to the user. As the Tempdb is separate database from the main database do I have to transfer the grouping tables every time to the Tempdb for this use ( ie via CREATE and INSERT ). So essential recreating a mirror of the Main database in the Tempdb for development use.
I guess I do, but just checking.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Digs SSCertifiable Points: 7333 More actions · Answer 1

Can somebody also explain how the SQL server with mutli user activity how the database works with objects( tables, procedures, etc)

Does it create a mirror set of permanent objects for each user, does each user become a "database owner" ?

Does a TSQL programming have to be written so that each user has its own set objects via Tempdb and #tables ?

I come from a MS Acess background and trying to learn best practises for TSQL design in multi user environment for building data sets for different WHERE clauses for each user runnung the same TSQL ?

I do understand TRANSACTIONS and locking when INSERTING data, but getting it out. Maybe its "read only" and there are no issues ?

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

You do not have to use temp tables at all unless it makes sense for your application. SQL does not automatically create a mirror set of objects for each user and each user does not become a database owner.

When you say "different where clause" for each user, do you mean that different columns will be used or that the value will be different?

I would suggest that you learn about stored procedures. That is considered the "best practice" for SQL applications.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Digs SSCertifiable Points: 7333 More actions · Answer 3

I will use TSQL, thats my question.

Say I have a TSQL code that creates a table and populates it with data for a @Customer and then I send the data for report via ADO to client.

How does SQL 2000 handle multi user when, two clients at the exact same time run the TSQL code for two different customers ?

Do I have to use locking, use user based tables like #table, what ?

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

In this scenario, you don't have to worrry about locking. You also do not have to use temp tables (#table) unless it makes sense in your application. If you do use temp tables to store information, then make sure you use only one # so each user has their own tables.

This is an example of how I would do it:

create procedure usp_CustomerReport @custID int as
select col1, col2, col3 from customer c join orders o on
c.CustomerID = o.CustomerID where c.CustomerID = @custID
Return 0

Another Example:


create procdure usp_CustomerReport @custID as
create #tempSums (customerID int, totals money)
-- some code to populate your table

select c.customerID, t.totals, ... from customer c join #tempSums t on
c.CustomerID = t.CustomerID where c.CustomerID = @custID
Return 0

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Digs SSCertifiable Points: 7333 More actions · Answer 5

Thanks for help..

My concern.."How does SQL 2000 handle multi user when, two clients at the exact same time run the TSQL code for two different customers ?"..

Say client1 WHERE clause is getting data for Customer BROWN, and client2 WHERE clause is getting data for customer SMITH...AT THE EXACT SAME REQUEST TIME.

My TSQL will perform this sequence of logic

CREATE TABLE, INSERT differnet types of data, GROUP them and return to client.

From your post #temp tables seam the way to go.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 6

Each user will have a different set of local temp tables unless you use global temp tables (with ##). Behind the scenes SQL actually names each local table with a unique name.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Digs SSCertifiable Points: 7333 More actions · Answer 7

For more info I searched "Ownership Chains" in SQL BOL.