> Hi all. I've always been told, and read here that using * in SELECT > queries is slower than specifying the field names. I just happen to > be working on a site now where I have a page with 3 big SELECT > queries. One of them with over 100 fields. If I use * in lieu of the > field names, the page execution time is reduced from +- 250 > milliseconds to +- 185 milliseconds, with the actual query times > being cut by about 1/3. > > Anyone else experience this? The 2 largest queries have no joins, but > even the one with a join goes from 50 ms to 35 ms if I use: > SELECT L.*,B.* > > Have I been reading this wrong? I've sure wasted alot of time > entering field names into Select queries , not to mention slowing > down my apps.
This is all dependant on the database engine you use, but here's why you shouldn't normally use "SELECT * FROM"
When you do SELECT * the database has to go to the "system" table and find the list of fields in that table, then effectively do a "SELECT field1, field2, field3" for you - this is increasing the access time in the database as it has to do an additional query for you
If you only want a couple of the fields, then it's a complete waste to get all field, although it can be advantagous doing a "SELECT *" as there's less info going to the SQL engine for the request for it to parse, which can be slower than the query speed itself...
Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133
"Websites for the real world"
********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. **********************************************************************
CF-Talk
Philip Arnold - ASP