Re: Can I optimize the engine here or do I have to get the developers to change the SQL?

On 9 Nov, 15:35, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > > First, you need to make sure you're not open to SQL Injection. ...that's a given. > Second, what happens if the values that the individual wants isn't in the first 200 rows? > This is a deliberate restriction put in place as a direct response to ignorant users who insist on being lazy and just querying for everything rather than thinking a bit harder and filling in the fields on their form a bit more intelligently. There are some other filters on some joins in the full query that I haven't given here simply because I've narrowed down the problematic section. You know, county, direct_mailing Y/N and so on. > I agree that post filtering makes sense, but what happens if you do a query and you post filter only to find that no rows matching were returned because you didn't specify the name_type = N ? name_type is a radio button on their form and they can't not supply it. > (And because you have 3 mil rows with only 8000 rows that have name_type = N, its very likely that this scenario could happen. You could fetch 200 rows with > name_type = P and post filtering yields no rows found. So they have to go back and try again with a more intelligent set of criteria. We hold their hands as much as we can anyway. > 8000 out of 3 million = 8/300,000 or ~ 1  in 37,500 rows fetched will match. > But hey! What do I know? ;-) More than many, less than others. > HTH All help & advice gratefully received! Although I take with a pinch of salt the valediction: > _________________________________________________________________ > Hotmail: Trusted email with powerful SPAM protection. Mind if I LOLPMP?