The Young DBA

When we run group by in mysql " group by list doesn't require as selected  list " . This is another myth and confusing for mysql users. There is a table structure and some test data which I ran on mysql and sql server simultaneously. Create table groupby_test ( id int,desg varchar (20), location varchar(20)); insert into groupby_test values (1,'Jr Software','Delhi'); insert into groupby_test values (2,'Jr Software','Noida'); insert into groupby_test values (3,'Software Software','Delhi'); insert into groupby_test values (4,'Sr Software Software','Gurgaon'); insert into groupby_test values (5,'Software Software','Gurgaon'); Then Executed below group by query on mysql and sql server. select desg, location from groupby_test group by desg; Result in MySQL; Result in SQLServer Msg 8120, Level 16, State 1, Line 9 Column 'groupby_test.location' is invalid in the selec

Hi, Many times mysql users asking me "show processlist showing list of many sleep queries on their environment". This post is for mysql user. Sleep meaning that thread is do nothing .  Its not a query waiting for connection. It's a connection pointer waiting for the timeout to terminate. and it doesn't have an impact on performance . To varify on your instance run below query.. show variables like '% wait_timeout%'   Then we will see setting is  default wait_timeout=28800 or something else as configured; So we can set values smaller,eg 10-20. We can also kill the thread status maked to sleep, that will not impact system. Thanks

Hi Friends, This is the 2nd part of my post " Why JSON Came into Picture " .  I am trying to more elaborate how JSON replaces XML and supported in technology upgrade. Background About JSON JSON stands for JavaScript Object Notation, and was first formalized by Douglas Crockford. JSON is a data format interchange - method of storing and transferring data. Mostly its uses such as data conversion (JSON to SQL) and exporting data from proprietary web apps or mobile apps. XML was a big buzzword in the early 2000’s, JSON become the buzzword in later few years. What are the Impacts JSON bring in technology ? NoSQL Document database became popular, mongodb was among one of lucky database vendor.  Technology found JSON as an alternate to xml for data interchange on platform independent. Technology found a supporting for RDBMS to keeping variety of data specially non-structured data in one environment and module. Data explosion and big data came into the platform. Th

Hello friends,  If you are talking about data, database , data-warehouse or big data nothing will complete without schema. Schema plays an important role in Data Platform.  Today I am exploring about Schema on write and schema on read in respect of datawarehouse and data lake.  Let see differences. Schema on write  Structured Data,  RDBMS,  OLAP / Data-warehouse.  Heavy ETL (extract-transform-load) role in data movement.  Change in data-model is costly.  work well in range of Data Mart. User have set of questions. Business Analysis. Collect  Data - Apply Schema - Write Data - Analyze. Schema on read  Structure & Un-structured Data.  RDBMS, NoSQ &  Hadoop.  BigData / Data Lake. ELT (extract-load-transform) & Low cost extraction. Schema is just a structured file can be switched dynamically. Ideal for large volume of data. User is exploring data without pre-defined query. Data science & Research. Collect Data - Write Data - Apply Schema- An

Data profiling Data profiling is the process of analyzing the source data to better understand, what is the condition of data. This task basically used in beginning of the development life cycle in OLAP or ETL application. So its related to OLAP and activities related to this process is data cleanings analysis, pattern analysis, number or nulls, aggregations, group , distinct values etc. Data Profiler is a tool available to do this. Query profiling SQL Server or any database technology provides utility or tool that help to discovery query and slow running queries. Query profiling is related to OLTP applications. This helps to performance tunning and optimizations. We do analysis on index scan/seek, query IO/CPU/Memory usage , physical and logical reads etc. Profiler is the tool in SSMS to do this.

What is JSON ? JSON stands for JavaScript Object Notation, and was first formalized by Douglas Crockford. JSON is a data format interchange - method of storing and transferring data. Mostly its uses such as data conversion (JSON to SQL) and exporting data from proprietary web apps or mobile apps. XML was a big buzzword in the early 2000’s, JSON become the buzzword in later few years.  Why JSON came into picture ? After 2005 applications & user requirements started growing rapidly, Hardware and software developed, the advent of Single Page Applications and modern mobile/web apps that we know today needed some kind of data interchange to function seamlessly. To fulfill user requirement technology started shifting into  new language-independent data interchange format that time JSON came into the Picture. JSON gained rapid popularity because it makes transferring data very easy. It’s also lightweight and easy to read and understand. There are few other reasons that JSON make

Recently I got a defiance from a some developers regarding postgresql server is slow performance. In his opinion it is not able to fetch 5 lac record from CSV files. They show there aplication which was using (node.js+sequlize ORM). When executed it was really sucking in middle. 5 lac records are really in huge amount but postgre is alos being for high performance application. There is no question we can say that it can not able to fetch this amount of data. So I suggested him to start importing from short amount of records like top 6 then top 600 then top 6000 then 60000. Yeah he did and really it stopped after 60000 records. It failed from application, program is running, running and keep running.. So boll goes in my bucket then I started digging on database server configuration parameters like effective_cache_size, work_memory, Shared_buffers, Maximum_number_Connections, wal_buffers etc, given appropriate values as per best practices and current system resources. Then re