I've a performance issue with using Scalar User Defined Functions(UDF) in queries.
There is a UDF fn_get(i int) which returns a scalar.. It holds lot of logic and performs normal scalar operations..
Actually
SELECT *,fn_get(i) FROM #temp1;
is fetching 10,000 results within 3 seconds and getting displayed in Studio UI.
Whereas,
SELECT *,fn_get(i) INTO #temp2 FROM #temp1;
is inserting the same 10,000 results into table #temp2, by taking time of >4 minutes
Don't know why difference is such enormous (3 seconds vs 4 minutes :O)
Am not sure, if this is the way to ask a question here.. Any guidance to improve the query performance is of great help..
Sybase IQ is a OLAP tuned columnar database. Which means, out of the box it is tuned for reads, not writes. So it would be normal for read performance to greatly outpace write performance, even on the same data set.
Now many things can affect write performance, storage type, IO bandwidth, caching, indexing are a few of the factors.
To get more detailed information on the particulars of your query, you should take a look at the execution plan. This will help break down where the system is spending time.
SAP has a detailed document on Sybase IQ execution/query plans. It may not be for the specific version of IQ you are running, but the information will be generally applicable.
Note: It is highly discouraged to use select * (ever) in a columnar database. The data is split and organized by column, so reassembling an entire row is a very costly procedure. Unless you absolutely need every column in the row, you should always specify which columns. It is also just general SQL best practice to always specify columns in your query, even if you are retrieving all of the columns.