Using SQL Server Views 2

[CP60]

If a SQLS View is based on a table with 100,000 records, and a report is built off of the view with a record selection formula, does CR XI pull in all the records and then filter the records based on the Selection Formula, or does it send the Selection Formula as part of a WHERE clause in the passed SQL Statement?

1. Underlying table has 100,000 records
2. Record Selection Formula would limit this to 100 records

will CR still first pull from the server to the client 100,000 records?

 

[dgillz]

The view would limit the recordset to how many records? This is the key piece of info.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[jdemmi]

It should be using the where in the CR to additionally filter the record set BEFORE returning any data to the report. You can see this by viewing the SQL query the report will pass to the DB (Via Database --> Show SQL query)


-- Jason
"It's Just Ones and Zeros

 

[CP60]

The view would limit the recordset to how many records? This is the key piece of info.

The View doesn't limit the number of records. Only the Selection Formula is in affect to do so.

dgillz, would you also agree with the below? I would also appreciate your input concerning this, using this additional information.

-------

jdemmi,
I looked at it and there is a SQL Statement with the proper WHERE clause.

 SELECT "vMyView1"."Field1", "vMyView1"."Field2", "vMyView1"."Field3", "vMyView1"."Field4", "vMyView1"."Field5", "vMyView1"."Field6"
 FROM   "MyDB"."dbo"."vMyView1" "vMyView1"
 WHERE  "vMyView1"."Field1"<'SomeValue'

So, it would seem this will limit the records right on the server, which is what I needed to know.

 

[dgillz]

Chilipepper,

A view most certainly could limit the number of rows, by using a where clause.

Without getting into details, not all record selection formulas in crystal actually translate into a where clause under database->show SQL query.

In these cases the use of a SQL view is much more efficient, and you don't even need a record selection formula in crystal if the view limits the recordset as desired.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[CP60]

dgillz
I wrote:
>The View doesn't limit the number of records. Only the Selection Formula is in affect to do so.

You wrote:
>A view most certainly could limit the number of rows, by using a where clause.

I guess I badly worded that. What I meant is that the View I have/use doesn't limit the records...

The problem I am having is I am not doing this in Visual Studio .NET, but in VB 6.

The filter criteria is generated dynamically based on what the user wants to see. This criteria is used in a SELECT statement which opens the View. And then the user sees the data in the application in a grid.
When the user wants to print a report, the same criteria needs to be used. So this Filter criteria is also passed to CR.

I used to use an access mdb on the server, and a local mdb with temporary tables created for the more complicated things with alot of data- it kept the server mdb from bloating when using temp tables. And also the data only needed to be pulled over the network once.
However, simple reports just had the filter used in a SQL Statement passed to the server from the app, and the same used in a CR Selection Formula.

Also, the collection of data came from several tables and had to do many other things too complicated in an access query - unfortunately, the mdb wasn't designed well.

The application would get the data from the server based on the selected filter criteria and then store the data locally.
Then the application would use this local table for the grid and when the user prints, CR is based off this local table.

Now I am putting all this under SQLS 2005 and trying to let SQLS do as much of the work as possible using SPs and Views.
These complicated dynamic filters need to be used by the application and the same by CR.

 

[beltmanjr]

Just realize that the view created on the database engine (MSSQL in this case) will always retrieve all database records that are valid for that view and does NOT care about any filters in Crystal or any other application.

The additional filters set outside the MSSQL server will then be applied to the data temporarily stored in the view.

Hence in your example your view will always collect 100,000 records before passing the required 100 to crystal.

This really becomes an issue if the collecting of those 100,000 records slows down the performance significantly (which I often have with some millions of records). In this case you may wish to look at temp tables, using the crystal command or stored procedures.

 

[CP60]

Getting confused:

beltmanjr,

Are you saying this:

1. Table1 contains 100,000 records

2. For this example, say Field1 is an Integer numbering all those records from 1 to 100,000

3. View1 is based on Table1 and has NO filter criteria

4. External sql statement executed is:

"SELECT * FROM View1 WHERE Field1<=100"


Are you saying the View1 will first query 100,000 records and then the SELECT statement will filter those to 100 records, and then these 100 records get passed back?

And that if I were to use a dynamic query directly off the underlying table as in:

"SELECT * FROM Table1 WHERE Field1<=100"

will in any case be faster, and if there are many many records and possibly JOINS, that querying directly off the table should be much faster?

This would also mean using a Stored Proceedure with SELECT statement on a View is slower than a table, using this example?

That would mean that the only possibility to limit thoses records effectively is to use a Stored Proceedure SQL Statement directly off the table, or a Dynamic SQL Statement directly off the table.

This would also mean if the Filter Criteria is complicated, too complicated to use Parameters with because of changing Filter criteria (like, one time using Field1 only= and the next time Field2= only), then either using a dynamic SQL, from the client (or in a Stored Proceedure EXEC(SELECT...), is best?

Using Temp Tables was one question I had in another thread, but I don't know how to get CR to "see" the same #Temp table created in a Stored Proceedure called from a VB 6 application.

 

[beltmanjr]

Temp tables:
I'm afraid that changing tables is slightly harder on the fly in crystal, but not impossible I'd assume. Within Crystal you can set the data location (under database menu) and change the tables individually. Perhaps using the SDK can help you here?

About the view and external Select statement:
Yes, your first statement is correct. The view will read 100,000 records and the external Select statement will filter these 100,000 down to 100 in a seperate operation.

Anything where you query a view that does not significantly reduce the number of records compared to the original source is slower than querying the original source directly.

Note that if your view reduces the orginial source from 10,000,000,000 records to 100,000 and you query the view it might be faster then directly querying the database from an external program (select statement). This because anything executed on the database engine directly is usually faster than an external query that uses some extra layer to query the database.

Joins:
The view is obviously great for joining tables, especially from different sources or with some outer joins with ON statements as Crystal doesnt understand ON statements all too well.

Changing parameters:
Do realize that you could always use a parameter in the crystal record selection criteria like:

({?parameter1} = '*' OR {?parameter1} = {Table.FIELD1})
and
({?parameter2} = '*' OR {?parameter2} = {Table.FIELD2})
and
({?parameter3} = -1 OR {?parameter3} = {Table.MyNummericField})

Since stored procedures are not guaranteed to work by Crystal depending on the connection type (as far as I can remember) I'd avoid these.

Dynamic SQL:
If you can submit the sql for crystal to use in a crystal command using the SDK (and I dont ever use the SDK myself) this would be a very simple way of fixing the issue, especially if you cant use Crystal's normal way of using tables and linking them as with sql containing complicated joins or unions.

Hope this clear things up a bit.
And remember that your current way doesnt have to be slower then using the tables directly, but might be.

 

[CP60]

beltmanjr, Thanks for all the info.

You wrote:
>Within Crystal you can set the data location (under database menu) and change the tables individually

Yes, I know this. But how do I find the #TempTable in SQL Server for CR to use?! That's my problem.
If process #1 runs a stored proceedure and creates a #temp table, if I pass the #temp table, CR will not see it.

dgillz, I am still very interested in what you have to say since according to your profile it looks like you do alot with CR

 

[beltmanjr]

I'm certain both dgillz and lbass can tell you much more about this.

Since I am not the biggest star with the whole .net side of crystal and creating temp tables in MSSQL we need to accept that I might put some silly questions down in my attempt to help you.

1. If you get process #1 to run a stored procedure creating a temp table, how would you locate this temp table on the database server?

2. I would advise against creating a temp table everytime you run a report as this may even be a larger overhead than using a view. Temp tables that severly limit the number of records from the source or do some complicated joins should be created some time when the system is less busy (like at night). Problem with this is that your data is not 100% fresh which you might want if reporting on a transactional database.

3. If you can locate that temp table in your database aplpication is there a way of retrieving the location/name of the table using some function during the creation or can you even set its name and location?

Google probably helps with this issue.