►Q◄ SP (Stored Procedure) vs. FIND action.

BenHayat

Question for those who switched to SP in their app.

Q1: Should we switch to SP from FIND when data volume gets large? For example if I have a simple query like
FIND Customer where Customer.Balance>100
Here the condition is simple where volume becomes the question. Let's say, with this simple condition, If I had 10M records, will a "FIND" take the same time as a SP with a simple Select statement for Customer.Balance> 100?
Another words, should we consider "Data Volume" as a factor to switch to SP?

Q2: Should we switch to SP if the FIND statement is too complex regardless of "Data Volume". Will a SP run faster with a complex SELECT statement that Aware's FIND statement?

BLOMASKY

Ben, the simple answer is NO.

A stored procedure is a compiled SQL statement where it has been checked for syntax errors, and depending on the back end, it might find the least cost (fastest) way to run. This only takes a very short time, so you won't notice any speed improvements no matter how large the db table is.

However, if you are doing something complex that you can NOT accomplish in 1 complicated SQL statement, (i.e. if you have to have multiple FINDs, then a SP can be much faster to run (and perhaps, easier to write / understand / document).

I probably have 10-20 that I use in my application, but all are multiple nested queries or they are queries with temp tables, or they are dynamic sql statements where the where clause is dynamically created (where both sides of the "=" statement can dynamically change).

Bruce

p.s. You didn't ask about the network traffic issue. If I can have the server do more of the heavy lifting and instead of returning 1000s of row to have Aware only use a few of them, then a SP might be the way to go. But that's another topic.

aware_support

Adding to Bruce's reply. Generally you should only use SP's when you find that Aware IM query is slow. Simple queries like the one in your example shouldn't be slow (if you are searching really big data volumes then the speed can be improved by a simple indexing of an attribute that you search by). It's the complex queries that can be slow, but again it really depends on the query and the volume of data.

The reason a stored procedure can dramatically improve the speed of some complex query is that Aware IM uses a generic algorithm to translate a query from Aware IM Rule Language to SQL. However, for a very specific query this algorithm can often be optimised.

BenHayat

Thanks!

BenHayat

BLOMASKY wrote
Ben, the simple answer is NO.

Thanks Bruce; But I was asking two separate and independent questions and you came up with "Ben, the simple answer is NO." and things that really didn't relate to direct questions.

1) Should we consider "Data Volume" as a factor to switch to SP?
According to Support, this shouldn't matter between SP and FIND.

2) Will a SP run faster with a complex SELECT statement that Aware's FIND statement?
Also, according to Support, it seems, I should only jump on SP if my FINDS are slow to resort to SP.