cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
39
Views
0
Helpful
0
Comments
cdnadmin
Community Member
This document was generated from CDN thread

Created by: Janine Graves on 24-10-2007 09:42:19 PM
when using a db element to select data from a db, is there a limit on the amount of data that can be retrieved? For example, what happens if the db returns 1G of data? What's the limit?

Is this configurable?

Thanks, Janine

Subject: RE: db memory limit?
Replied by: null on 24-10-2007 10:56:50 PM
Hi Janine,

There is no artificial limit imposed by the Database element on the size of the response. So for example, entereing "select * from mytable" could returna huge amount of data and the database element will dutifully attempt to handle it. There is a chance it will handle it correctly (assuming the JVM has enough memory allocated), but could easily cause a major delay in the voice application potentially incurring a request timeout.

Obviously it is up to the user to ensure that the query is not too broad. There is no way in the database element to limit the size of the response, though I wouldn't be surprised if SQL had a way of limiting the response.

Subject: RE: db memory limit?
Replied by: Michael Bochynski on 24-10-2007 11:37:21 PM
Janine,

Just to add to Elam's response, developers should make sure themselves that any data, not only DB query, is not too large or will not slow down Call Services.

As far as SQL commands are concerned, the easiest way it to limit the number of obtained rows and/or columns.

For example, you can limit the number of rows by using LIMIT :

[i:6babe714d4]SELECT * FROM mytable LIMIT 0,10[/i:6babe714d4]

This will cause to select only 10 rows, starting from row 0.

Best practice for SQL queries is to avoid broad queries, such as "SELECT * FROM table" and later process it with your code. You should limit the data obtained to the data you actually need, by modifying your SQL query and getting the data you actually need. This would greatly improve performance of not only Call Services but also interaction with database and database itself.

Hope it helps,
Michael

Subject: RE: db memory limit?
Replied by: Janine Graves on 24-10-2007 11:51:12 PM
thanks, I didn't know about the Limit command. I'll pass on the information to my students.

Subject: RE: db memory limit?
Replied by: Michael Bochynski on 25-10-2007 01:13:20 AM
Janine,

I'd suggest to have the database queries created by someone with DB experience, as those might greatly improve performance. Besides LIMIT you can use ORDER BY to sort your results in order to limit to only top 3, for example. You can also use WHERE clause to add conditional selects. Not mentioning even nested selects, assuming that your DB engine supports it.

In general, database queries are complex and can affect performance a lot. If you try to get too much data with one query, you might get timeouts from your voice browser.

MySQL posts a very good tutorial for SQL. It is available at:
http://dev.mysql.com/doc/refman/5.0/en/index.html

Michael
Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Quick Links