Best strategy? How to filter Report results

Hi,

I’m using SQL Server 2000.

I need some help on what strategy to adopt in order to filter some existing reports that I have written. I’m basically a web developer (ASP.NET) with some ? T-SQL skills (therefore I need advice on which is the best strategy please).

The business system I’m working on has 2 levels of access: Administrators (there can be several) and Users (20,000+).

Users can login and do stuff (do a task).

Administrators can login and give tasks to the Users to do. Administrators can also run reports to see the status of the Users relating to a particular task. A report on task X will show a list of all Users who have been given that same task (example of a task: “attend to a conference”) and next to each User will be the status (e.g. “Completed” or “Not yet completed”).

That is the existing system (above). Now I need to add a new type of person who can login and view the same reports as the administrator. This type of person will be called a “Manager”. The difference is that the Manager will only be able to view information on a defined set of Users. There might be a few hundred “Managers”.

So my “Wizard web page” code that allows an Administrator to create a Manager account will allow the Administrator to select (from a list of Users) which Users this Manager is allowed to see.

This means I’m going to have a new TABLE to relate the Manager to the User. I’ll call this the tblReporterUser table.

This table will have a ReporterID and UserID (read strategy #1 for reason this is not called “ManagerID”).

Here are the strategies I can think of. Which is best or is there one even better?

Strategy #1

As well as filling the tblReporterUser with Manager to User relationships, also fill it will Administrator to User relationships.

This means that when I call the Stored Procedure to select the rows of my report I need to pass in the ReporterID (which will actually be a ManagerID if it’s a Manager running the report, or an AdministratorID if it is an Administrator).

In order to filter Users out of the report I would then use this ReporterID to JOIN against the tblReporterUser TABLE.

Disadvantages: Each time Users are added to the system I have to make sure all administrators get permission to view the new User (add a row to tblReporterUser TABLE for each administrator with the new UserID).

Also it might make reporting for administrators slower?

Strategy #2

Explained with some pseudo-code (I don’t like this because of the “repeated code” as its easy to get into maintenance problems):

If (RoleIsAdministrator) THEN

-- role is Administrator

Existing report code

ELSE

-- role is Manager

Existing report code

JOIN on the tblReporterUser using the ManagerID as the ReporterID

ENDIF

Strategy #3

Is there a way to do a “conditional” JOIN ON the tblReporterUser if the “role” is Manager (but don’t do the JOIN if the role is Administrator).

If there is a way then this sounds like the best solution.

I have no idea how to do a conditional join – could someone give me an example please?

Massive thanks in advance to anyone replying with any amount of help.

Cheers,

Just back from 5 days camping. It rained! :D But in Australia that is a good thing!!!!

I guess this is a *BUMP* for this thread. If anyone has any ideas about "conditional JOINs" please help.

Many thanks,

Chiz

sql

Best solution, iterate over millions records and call extended sp

Hi,
I need to iterate over millions rows in a table and I need call an extended
stored procedure (written in C++ and not possible be written in TSQL) using
the columns of each row as parameters and write the return values to an new
table. The current script open a cursor.
What's the best way to implement it? BCP to a text file and external program
parse and write the text file and BCP back? sp_cmdshell an executible for
each row? (so needn't worry about C++ memory leak issu). XML?....
Thanks,"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,
I'd say the "best" solution would be to rethink your architecture and
implement it differently if you plan to do this on a regular basis. This
doesn't sound like a very scalable or desirable way to use a client-server
database. Have you considered using SQL Server 2005, where you can implement
.NET code in the database? Or implementing your code in ADO rather than wit
h
an XP?
If it's just a one-off requirement then you just need to test which approach
works best for you. It isn't really a SQL question since, for the purposes
of this exercise, you are just using SQL Server as a file dump rather than
what it was designed for. Why not just loop in TSQL and call the proc for
each row?
David Portas
SQL Server MVP
--|||If you have a situation that calls for looping through a cursor, then it's
better to implement the cursor on the client side than on the server. Open a
read-only, forward only ADO recordset and Command.Execute the stored
procedure for each row.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,sql

being able to stop parallelism in a C++ Program calling SQL Server

Hi All,

I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.

One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.

However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.

Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.

Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?

Thanks in Advance.

Peter Nolan

www.peternolan.com

select * from tablename OPTION (MAXDOP 1)

That should do it. MAXDOP stands for Max Degree of Parallelism, and allows you to limit the number of parallel execution threads for a query. Setting it to 1 essentially disables parallelism for that query.
|||

Hi David,

thanks very much for that....I did not know the syntax of how to do this at query level....

We turned off parallelism at the server level and it turns out the optimiser will still perform a table scan....we have many logical tables in on physical table and even when we query the underlying table with a select * from with a where clause it is scanning the table and taking about 40 seconds to do so....even when we say 'top 1' to make it get the first row and put an index on the field that logically partitions the table.

It looks like we will have to introduce partitioning so that the optimiser can know to scan just the partition in question. We also had a feature we planned to introduce which was to turn off table validation....if an etl subsystem is in production there is little need to validate tables....we never put it in...and now there seems like a need to do so.

Again, thanks for the tip. Much appreciated.

Peter

www.peternolan.com

|||

Hi All,

one of our guys here came up with a better idea....

What the tool is doing is validating the existence of the taget table/views passed to the program....so in the case of a typo a specific message saying the table/view not found is issued.

However the optimiser is thinking the program really wants all the rows.

One of the guys here suggested if the constraint where 1 = 0 is added to the select * then all optimisers will be smart enough to know no rows will be returned yet the database will still check that the table/view exists.

So we will also implement this test and that will make sure all programs test for existence of a table quickly.

If we feel industrious one day we can actually ask ODBC if the table exists.

One reason why I like the select * from tablename as a test for existence of a table over just asking ODBC is that it forces the preparation of the plan and if the view has been invalidated then it will also be caught at this stage of processing where ODBC will return a positive answer to does the view exist...

Again, thanks for your responses...it is much appreciated...

Just by the way....we installed the new performance dashboards just released and it was these that managed to show us the statement that was in a wait state and how we detected the problem...so if you have not installed the new performance dashboards yet....you might want to do so...

Best Regards

Peter

Beginner help stored procedures, also .net

Hi,
I'm not qualified enough to be called a beginner -- I could use some help.
I've written a few fairly simple queries and run them through the query
analyzer. I'd like to know 2 things:
How can I turn them into stored procedures?
How would I then run those stored procedures through vb.net?
Any help would be appreciated.
Thanks,
ArtArt
CREATE PROCEDURE myFirstSp
@.par INT
AS
SELECT OrderDate FROM Orders WHERE OrderId=@.par
--usage
EXEC myFirstSp @.par=10248
You will find more info about stored procedures in the BOL.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art|||You can find samples and tutorials on MSDN.com and ASP.net
"Art" <Art@.discussions.microsoft.com> wrote in message
news:DEF405CC-9E63-4D9A-8405-A281CC991ABD@.microsoft.com...
> Hi,
> I'm not qualified enough to be called a beginner -- I could use some help.
> I've written a few fairly simple queries and run them through the query
> analyzer. I'd like to know 2 things:
> How can I turn them into stored procedures?
> How would I then run those stored procedures through vb.net?
> Any help would be appreciated.
> Thanks,
> Art