Workload optimization – Are you balanced?

0

548282963The other day I was chatting with an ETL developer and he said he always pushes queries into the database instead of dragging data across the network. I thought “Hmm, I remember talking about those topics when I was a DBA.”

I'd like to share those thoughts with you now.

Most databases, used for business intelligence, are not overly saturated with transactional type queries. This leaves resources (platform power and memory) available in the database environment that could be used for complex queries. Queries that may join multiple tables together for an ETL process or program. That said, writing that query properly will be very important.

The important thing about writing a good query is to understand the indexing and the data types. I wrote a blog a couple of years ago called VARCHAR(255). I was amazed that many attributes in database tables are defaulted to this data type. These attributes are not good candidates for indexing, nor will a query be quick and efficient on attributes with textual content of VARCHAR(255).

That said, if you are writing a complex query that joins multiple tables, that has many rows of data, then consider pushing that query to the database using the techniques that work best. For example, SAS has magic ways when using Teradata.

If you are querying for specific indexed rows that may join one or two tables, consider letting the ETL server bear the burden of the process.

white paper

Data integration paper

Balancing the workload is an ART. Those that do it every day are worth their weight in gold.

So, am I balanced? NEVER! But, hopefully you have an ARTIST who can help optimize your workloads for those times when the database is queried the most. If not, then monitor those queries and look for the ones that are taking the most resources – start your analysis there.

Share

About Author

Joyce Norris-Montanari

President of DBTech Solutions, Inc

Joyce Norris-Montanari, CBIP-CDMP, is president of DBTech Solutions, Inc. Joyce advises clients on all aspects of architectural integration, business intelligence and data management. Joyce advises clients about technology, including tools like ETL, profiling, database, quality and metadata. Joyce speaks frequently at data warehouse conferences and is a contributor to several trade publications. She co-authored Data Warehousing and E-Business (Wiley & Sons) with William H. Inmon and others. Joyce has managed and implemented data integrations, data warehouses and operational data stores in industries like education, pharmaceutical, restaurants, telecommunications, government, health care, financial, oil and gas, insurance, research and development and retail. She can be reached at jmontanari@earthlink.net.

Related Posts

Leave A Reply

Back to Top