ramarcov
Posts: 4
|
| Posted: 07/11/2006, 3:03 PM |
|
I have had no issues with basic queries and where clauses but when I use the Report Builder to search for rows with a date or any other search it appear that Code Charge wants to bring the entire table back and then let you search. That is not how most SQL works. My table has 1 Million rows so when I do the report I only want it to bring back what I am searching on. I read in another forum topic something about a first load. My question is how do I just query the rows that I am searching on. My queries time out when I publish since it appear it is trying to bring back the entire Million rows.
Any help would be appreciated.
|
 |
 |
peterr
Posts: 5971
|
| Posted: 07/11/2006, 8:13 PM |
|
I think that CCS doesn't have a choice on how to search the database, and it's not possible for it to retrieve millions of records when the query has a proper WHERE clause that returns only a few records. CCS can only send an SQL query to the SQL server and wait for results. If the database returns millions of records then it can only mean that the search (WHERE) statement is wrong. Or if the query takes long time to execute then it could mean that your database indexes are not optimal, or you're trying to summarize a lot of data, etc.
What have you done so far to confirm that your slow query used in CCS runs much faster in MS SQL Server?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
ramarcov
Posts: 4
|
| Posted: 07/12/2006, 6:48 AM |
|
Thanks for your reply. It is not the where clause. Since I have been a DBA for my company for 10 years I assure you I know indexes etc. This same query runs on our web page using both Net.Data and Local ASP code in less than two seconds. Most web query tools I have used in the past usually put the Search prompt on the screen first then once you enter your input it then goes out and runs the query. I had mentioned that I had read another forum question where a user answered that you have to fool the initial load. I tried that by adding a hard coded clause to the Where statement in front of the search function of CCS. I simply hard coded a date that would not be in the table. When published it came back in a couple of seconds and then let me input my Search Date which in turn gave me my data in a timely manor. So my question is what don't I understand about CCS's way of doing searches and maybe I need to go in another direction. Anytime I use the Report Builder and tell it to search on a column it always brings back data before I get to enter what I want to search for. Am I confusing you?? What I want to appear when I click on the link is just the What Date do you want to search Prompt. Then once I input the date CCS goes and runs the query on the table looking for the date I put in.
Here is the code I had to use so the Search would not bring back the entire table. This is not the one searching on Manufacture Date. I did the test on searching for a pallet.
SELECT COMPANY, DC_ID, LOCATION_ID, PART_ID, TRANS_QTY, TRANS_DATE, TRANS_TIME, SHIFT_DATE, SHIFT, PALLET_ID
FROM TRACE_VIEW
WHERE ( PALLET_ID='2222' )
OR PALLET_ID LIKE '%{s_PALLET_ID}%'
ORDER BY TRANS_DATE, TRANS_TIME
|
|
|
|
DonB
|
| Posted: 07/12/2006, 7:18 AM |
|
OR PALLET_ID LIKE '%{s_PALLET_ID}%'
is not going to use an index, for what that's worth. A LIKE that starts
with '%' cannot utilize an index.
CCS Data Sources have a 'default value' property, which is used whenever the
URL parameter is not supplied. I like to specify '-1' (or whatever value is
known to not occur in the real data) to short-circuit the grid's initial
load (when the search parameters have not yet been provided).
If the URL parameters (like 's_PALLET') are not supplied, the associated
term of the WHERE is not included when the query executes. SO you normally
end up with no WHERE at all when no search parameters are specified.
Personally, I like to set the grid 'not visible' whenever there is no search
criteria (in the Before Show event of the page):
If CCGetParam("s_SearchParam","") = "" Then
boolShowGrid = false
Else
boolShowGrid = true
$myGrid->Visible = boolShowGrid
This has the effect of not even invoking the grid's data source at all,
until there is data to display. Empty grids are useless anyway.
--
DonB
http://www.gotodon.com/ccbth
|
|
|
|
ramarcov
Posts: 4
|
| Posted: 07/12/2006, 8:16 AM |
|
Great response. I believe you have answered my question. Your comment:
(If the URL parameters (like 's_PALLET') are not supplied, the associated
term of the WHERE is not included when the query executes. SO you normally
end up with no WHERE at all when no search parameters are specified)
Explains what I have seen in my Report Builder Search Queries.
Yes you are correct about the % not using indexes but in this case the data did return rather quickly. This is why forums are so good someone usually has the answer. Thanks again.
|
|
|
|
|
