Re: [sqr-users] SQR vs. Stored Procedures in MS SQL 7 & 2K

[Buddy Sandridge <mes3@emcc.net>]

At 03:13 PM 11/29/2002, geek.unlimited@mindspring.com wrote:
> > If a complex stored proc were directly ported
> > to SQR with no changes, would both run equally
> > fast?
>
>Yes.  When writing new SQR you can reliable build
>and test the SQL for performance first and then
>drop it into the SQR.

Actually, it would not run *AS* fast since the network transfer rate would 
slow down the SQR unless you are doing almost no process, or you are 
generating more output after the processing.  (A good examples would be 
taking 10 rows from 2 tables and producing the Cartesian product.  SQR 
would probably be faster since you only transfer 20 records instead of 100).

> > Does SQR somehow make it easier for developers
> > to write poorly performing SQL?
>
>I see why you'd ask that, but it is too sweeping
>a question to answer with a definitive yes.  As
>long as you pay attention to what the SQR does to
>your SQL, you'll be OK.

One comment on this is that SQR may be wrapping your calls as stored 
procedures.  I don't have the documentation in front of me, but I worked 
extensively with SQR against both Microsoft and Sybase and the ODBC layer 
caused many issues that had to be worked around.

> > Since SQR seems oriented more toward Oracle and
> > other DBs, is it handled in an inefficient way
> > by MS SQL?
>
>I don't know enough about Oracle to address your
>question, but SQR was written by Sybase, back
>when it and SQL Server were still the same
>product.

Actually, SQR was Not written by Sybase.  It was owned by a Sybase 
subsidiary, but Israel Stern (not sure if that is spelled right, but he was 
the author) originally wrote it, not any of the database companies.  It had 
a much stronger tie to Oracle prior to it's changing so many hands ( about 
4 or 5, I can't keep track at this point!).

However, to answer the original question since I have work with all three 
databases (total of 12 years of SQR experience!), it is not written to be 
inefficient specifically, but by using ODBC, you have an inherent issue of 
an additional layer.  For example, with Oracle, SQR accesses the Oracle 
DLLs natively.  However, With ODBC, SQR accesses the ODBC DLLs which access 
the SQL Server (or even Oracle!) DLLs, so that extra layer is inherently 
slower by a very slight amount.  However, I did not notice a significant 
different when running Sybase and MS on equivalent boxes with similar data 
environments.  The reports were identical since we developed one code line 
to run on 5 different environments (Sybase on AIX, HP-UX, Solaris and NT, 
MS SQL on NT).  As was stated, the SQL can make a big difference.

Things that I have learned to watch out for is pulling many rows that will 
be excluded, or extra data that is not often used (or data that is 
repetitive...  look-up tables can be a real savings!)  Also, run every 
stored procedure in a SQL tool to get an idea of it's execution time.  Any 
data manipulation that can be done on the server could be a big savings if 
your network is a bottleneck for you.  We made work tables and would 
process almost everything on the server before doing a simple select back 
to SQR.

I hope these ideas help, there are many other possible ideas to help 
specific problems, but it depends even more on the data you are reporting 
on.  It really does relate back to the SQL a whole lot.

Thanks,
Buddy

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users