My process includes following processes for various clients.
- loading excel file into a sql database
- Processing loaded data in sql tables
- Dumping excel reports

I implemented these three stages by using set of physical temporary sql
tables (not the memory ones with # and ##) for data transformations
etc. I used to delete the data in these temporary tables and reuse for
the next client. Till now I was the only person taking care of complete
thing.

Now I want to add multi-user capability to the system. I feel the only
way to implement this is to change all my physical temporary sql tables
to memory temporary tables i.e. using (# ## tables). But sql statement
in data transformation task does not takes # tables. So I will have to
dynamically change the sql to have # tables.

Is it ok to load 20k records into memory for each client at the same
time. Or is it a good idea to use physical temporary tables i.e.
seperate physical table for each client and delteting it at the end.
Also implementing client level locking in separate table.

Am I thinking in right direction. Please suggest.

Hello pm,

AFAIK the # and ## are not memory tables they are tables in tempDB with differing
visibility.

The issue you will face even before getting there though is to do with Excel.
Are people going to get the same Excel file?, dump to the same Excel File?

Allan

Thanks Allan.

I am thinking to implement process level locking using database flags.
I will be importing and exporting to the same client specific excel
file whoever the user might be.

If one user is taking care of importing or exporting for a client, any
other user will find this process locked and won't be able to go ahead.


So whenever a process is happening for a client, I will insert a record
in the lock table. After process is completed, I will delete the
record. I might consider a user column also to tell which user is
actually locking.

Lock table
-----------

clientName LockedprocessName
client1 process1
client1 process2
client3 process1

Please suggest if this is a good idea.


Thanks,
PM


Allan Mitchell wrote:

Thanks Allan. That was a good point to look at.

I will be importing and exporting to the same client specific excel
file whoever the user might be.

If one user is taking care of importing or exporting for a client, any
other user will find this process locked for that client and won't be
able to go ahead.

So whenever a process is happening for a client, I will insert a record
in the lock table. After process is completed, I will delete the
record. I might consider a user column also to tell which user is
actually locking.

Lock table
-----------

clientName LockedprocessName
client1 process1
client1 process2
client3 process1

Please suggest if this is a good idea.


Thanks,
PM


Allan Mitchell wrote: