================================================ SEARCHDATABASE.COM'S DBA Tips and Tricks July 11, 2001 ================================================ Welcome to the searchDatabase.com DBA Tips and Tricks newsletter! Today's tip, "SELECT DISTINCT queries and derived tables" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci754605,00.html Don't forget about our huge collection of database administrator, developer, Oracle, DB2, and SQL Server tips FREE to searchDatabase members! http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html ******** Sponsored by Syncsort ***************** FREE handy multi-tool from Syncsort, the makers of the toolkit of data management software for achieving cutting-edge data warehouse performance: SyncSort is a powerful data manipulation, sort and ETL product. Visual Syncsort adds an easy-to-use GUI. Sigma is a specialized aggregation engine. FilePort is a two-way data conversion utility. Backup Express is an enterprise-wide, multi- platform backup and restore solution. Visit http://www.syncsort.com/tgb710 for FREE TRIALS, more information and a FREE multi-tool. ************************************************ "SELECT DISTINCT queries and derived tables" By Cade Bryant As most database admins and developers know, SELECT DISTINCT queries are quite resource-intensive, especially if you are querying a large table. Even if there are only three or four distinct values in the table, SQL Server must still search the entire table to get those. I was faced with this challenge recently. I needed to query distinct values on a column in a huge table, and I knew that all values would be represented within the first thousand records. Therefore, I thought I could quickly retrieve the data I needed by running this statement: SELECT DISTINCT TOP 1000 Type FROM BL_Print2 Yet even this statement, limited to 1000 rows, took an unacceptably long time to complete. More experimentation. Finally, I experimented with using a derived table to preselect a sampling of the data, and running my SELECT DISTINCT query on that derived table (a derived table is simply a SELECT subquery run within a T-SQL batch which creates a recordset from which the outer query selects). Bingo! The query completed in only two seconds: SELECT DISTINCT Type FROM (SELECT TOP 1000 type FROM bl_print2) t Here's how it works: SQL Server, for example, processes the inner query first, which returns the first 1000 rows of data. the outer query then runs the SELECT DISTINCT statement against this derived dataset, and since it only needs to search 1000 rows rather than the entire table, results are returned rapidly. ======================================= MORE GREAT STUFF ON SEARCHDATABASE! ======================================= More high-quality SQL tips, tutorials, and code: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281606,00.html Our huge collection of database administrator, developer, Oracle, DB2, and SQL Server tips FREE to searchDatabase members! http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html Listen to an audio chat with SQL guru Joe Celko: http://searchdatabase.techtarget.com/onlineEventsTranscriptSecurity/1,289693,sid13_gci558024,00.html The top SQL expert in the world gives a presentation on "SQL for Smarties" about advanced SQL techniques, and answers audience questions. He gives some great tips--check it out! Ask the Expert category of the day: "SQL" http://searchdatabase.techtarget.com/ateAnswers/0,289620,sid13_tax285649,00.html The structured query language is the lingua franca for accessing and updating data in relational database management systems. Ask your difficult SQL questions in this category. Our Featured Topic this week: "Database Replication" http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci752470,00.html Free, open-source DBMS's such as MySQL and PostgreSQL are becoming viable alternatives to the Big 3. But can they truly handle the demands of enterprise applications? Learn more with this collection of insights and advice. Fabian Pascal's latest rant against XML data management: http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci752631,00.html Hot topic in our Discussion Forums: "Referential integrity violence in MS-SQL" http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html ======================================= LEARNING ZONE FEATURED BOOK OF THE WEEK ======================================= "Teach Yourself ADO 2.5 in 21 Days" By Christoph Willie This book's coverage includes basic implementation of ADO 2.5 (i.e., data manipulation, working with recordsets, using stored procedures), advanced techniques (i.e., how to work asynchronously, data shaping, building applications, database management), and using ADO in a distributed environment (i.e., designing COM+ apps, the in-memory database, disconnecting data, using remote data service, debugging and tuning). http://www.digitalguru.com/dgstore/product.asp?isbn=0672318733&ac_id=58 ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as Oracle, DB2, SQL Server, database design, SQL, performance tuning, etc. Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ================================================ If you would like to sponsor this or any TechTarget newsletter, please contact Gabrielle DeRussy at [EMAIL PROTECTED] ================================================ If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
