Number of Tables 1

[petperson]

Hello,

Still quite new to RS, I am learning the tool with books and by just playing with it. Is there a limitation as to how many tables you can add in a dataset? Does it get complex as to how the tables can be joined together? Are you able to specify the join relationships? Just curious as to how many tables you have been able to sucessfully use in one dataset? I guess I'm kind of looking for some 'best practices'.

thanks!

 

[xlbo]

I don't believe there is any limit other than your patience and the timeout properties of the4 database being queried - certainly not in RS. It may not be able to VISUALLY display the query but it should accept and valid SQL. At the end of the day, you could always buil;d a view and query that view anyway - even if there was a limit...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

Best practices? Create your datasets so they call stored procedures. You get several benefits.

1) The users can't hack your report and find out what tables you are calling or what the table structure is.

2) A Stored Procedure's execution plan is stored (after the first time they've run) in SQL Server, giving you faster run time on the data request than if you run it directly from a Select statement dataset in RS.

3) It puts the burden back on the SQL Server instead of splitting it between Client, Server and your connection.

Reason #1 is good enough for me, but #2 and #3 help. @=)

Views are okay to be used (they too hide the table info), but they can be slower than SPs.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[xlbo]

Catadmin - I am not really a database guy so pardon my question if the answer is obvious but why is a stored procedure any quicker to query against than a view ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

The execution plan for a view isn't stored in SQL Server like the execution plan is for a Proc. This means, usually, that the server has to take extra "prep" time to figure out how it wants to pull the data for the view. I.E., what indices it wants to use, how it searches the tables involved, etc. After the first time a Proc is run, the system tables actually store the info for the Proc so that extra "think" time is taken out of the equation.

Realistically, if the view is a simple one (no joins, etc.), you won't notice a difference. But the more complicated a view gets, the more you will notice the time differential.

Of course, this is all dependant on whether or not the Proc was created with the WITH RECOMPILE option. If it was, then the point is moot as SQL Server will recompile and create a new execution plan for the Proc every time it is run.

Does that help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[xlbo]

yes - thank you very much

star for the useful explanation

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Catadmin]

You're welcome.

And thanks for the star. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???