What is meant by query optimization

SQL Query Optimization (Pervasive) - SQL, Query Optimization, Pervasive-SQL

I have a PHP application that pulls order information based on a scanned / entered order number for ordering purposes. We use Pervasive SQL, but the syntax is the same as MS SQL.

I've used a couple of smaller queries to break the kit apart into component elements that are called as needed but are easy and quick to run. My problem is our main query, which is where the most needed information is entered. They're quick for most orders because they're relatively small (1-15 items or so). My problem is with large orders (e.g. wholesale) that can end up being more than 100 items.

I understand that the more information is required, the longer it takes to run a query. I hope, however, that my query has room for further optimization.

In the query below, does anyone see something that I can tweak to speed up large order processing?

Reply:

1 for the answer № 1

You can't get any better than that, verify that every value you look up (including joins) has indexes and that you are not querying every item, make sure you request all the elements (relevant to the current page) at the same time and then process them on the client side.

Basically, it's not a query problem, it's either your code using the query results or the database design (or the network, but that's most likely out of your control).

I think I should also mention the glaring vulnerability in this line of code, but I'm sure you understand that very well.

Edit: I actually thought of something, if these fields don't exist, you should do them on the server side instead of sending thousands and thousands (or more) of spaces at the end of each column.


1 for the answer № 2

You probably can't get any better. My professor in a database course once said, "Don't try to be smart." The RDMS is good at optimizing "- with one exception, RDMS doesn't set indexes automatically. So make sure you have indexes on the fields you sort on, join, and select (selection as in the Where clause ).