May 2nd, 2010 by DLM
If you ever wanted to hide a form on startup in a .NET you figured out that the task is not so obvious. If you just set Visible property to False you will see that it will be ignored.
The solution is to run the form as Minimized, by setting the form property WindowState to minimized and in Form_Load function to set this.Visible = false;
Please note that line
this.WindowState = System.Windows.Forms.FormWindowState.Minimized;
must be in InitializeComponent() function generated by designer. Either you add this line manually there or set the WindowState property in the Form designer. At the very end functions which hides your form on startup looks like
private void InitializeComponent()
{
this.SuspendLayout();
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(292, 266);
this.ControlBox = false;
this.Name = “Form1″;
this.Text = “Form1″;
this.WindowState = System.Windows.Forms.FormWindowState.Minimized;
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
}
private void Form1_Load(object sender, EventArgs e)
{
this.Visible = false;
}
March 28th, 2010 by DLM
If you need to search for a particular word(s) or number(s) in a whole database you can use DBTYP.NET Database Full Text Search module which has been just added to the DBTYP.NET Studio.
It performs searches on all data types except binary, xml and date/time values. So you are free to search for whole words, part of words using SQL LIKE wild-cards or even for the numbers.
March 26th, 2010 by DLM
If you need to review configuration options of some SQL Server instance, you can get them out of the SYS.CONFIGURATIONS catalog view. So the select statement like
select * from sys.configurations
order by name
will give you back all configuration options of SQL Server instance.
March 25th, 2010 by DLM
If you reach a requirements to prevent some table for deletions or/and updates you may wonder how to reach that. The simple answer is to use INSTEAD OF triggers. INSTEAD OF triggers override the standard action of triggering statement: INSERT, UPDATE or DELETE. On that way, INSTEAD OF trigger can ignore parts of the batch, not process part of the batch or taking an alternative action. The major difference to the AFTER triggers is that INSTEAD OF triggers can be defined on a views as well as on a tables.
So, a solution of the problem, preventing a row deletions on the table can be the following INSTEAD OF trigger:
CREATE TRIGGER trgPreventRowDeletion ON Test_table INSTEAD OF DELETE
AS
BEGIN
REISEERROR (‘Deletions are not allowed from the Test_table’, 16, 1)
END
On a similar way it can be defined INSTEAD OF INSERT and INSTEAD OF UPDATE triggers.
March 25th, 2010 by DLM
The first thing you are doing when identifying poor query performances is to take a look at execution plan and indexes usage. Even that you see, everything is fine, your index can be fragmented so that SQL Server needs to make unnecessary data reads to execute query. At the end, your query is slow.
At the moment when index is created, little or no fragmentation is present. During the time, when updates, inserts and deletes occur indexes get fragmented what is a real bottleneck in a SQL Server performances.
There are two ways how to fix fragmented indexes: reorganizing or rebuilding them. Which operation is necessary depends on the level of fragmentation. Reorganization of index is suggested if fragmentation level is less than 30%. If it is more than 30% than rebuilding index is better choice.
Identifying fragmented indexes
Starting from version 2005, SQL Server contains a number of DMVs and DMFs qhich allow us to retrieve informations about SQL Server health and performances and identifying problems. One of them, allow us to take a look at the index fragmentation level – sys.dm_db_index_physical_stats DMF. It is important to said here that it places intent shared lock (IS) on the affected tables during execution.
sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)
The following query will get fragmentation of all indexes in the database
DECLARE @dbId int
SET @dbId = db_id(‘YOUR_DB_NAME’)
SELECT s.[name] AS SchemaName, t.[name] AS TableName, i.[name] AS IndexName, p.[index_type_desc], p.[avg_fragmentation_in_percent]
FROM [sys].[dm_db_index_physical_stats](@dbId, NULL, NULL, NULL , ‘DETAILED’) p
INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
INNER JOIN [sys].[schemas] s ON t.[schema_id] = s.[schema_id]
INNER JOIN [sys].[indexes] i ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE t.[is_ms_shipped] = 0
To get more proper candidates for rebuilding or reorganizing indexes it is necessary to consult other fields returned back from [sys].[dm_db_index_physical_stats] like avg_page_space_used_in_percent which indicates on average how full each page in index is. The higher number is better while but it is necessary here to balance fullness against the
number of inserts into index pages in order to keep the number of page splits to the absolute minimum. This exceeds the topic of this blog and requires adjustments of index fillfactor and monitoring of page splits.
February 24th, 2010 by dlukovic
In version 2008 our cross database comparison tool DBTyP.NET will support cross data comparison between SQL Server and MySQL DateTime values (date, datetime, smalldatetime). Actually, this is a common problem, between MySql.Data.Types.MySqlDateTime and System.DateTime types in .NET. It is very possible that if you tries to update MySqlDateTime column with DateTime variable you will get SystemArgumentException saying that could not store value … into … Expected type is MySqlDateTime. It is possible to format value as “yyyy-MM-dd HH:mm:ss” but in our case we don’t want to iterate through whole DataSet and make this formatting.
Very elegant solution is to change a DataColumn type from MySqlDateTime to System.DateTime as
myMySqlDateTimeColum.DataType = typeof(System.DateTime);
After that you should not worry about formatting.
February 24th, 2010 by dlukovic
A long time I have not published anything. BYPsoft preparing new version of DBTyP.NET with full support for a Oracle schema comparison and therefore didn’t had any free time left for this blog.
A very interesting topic came to me out of this experience.
Have you ever tried to get a list of all stored procedures and functions in Oracle schema? Preaty easy,
SELECT * FROM USER_PROCEDURES;
You figured out that it is quite different comparing to SQL Server and MySQL where with the single query
SELECT * FROM INFORMATION_SCHEMA.ROUTINES;
you get a body definition as well.
To achieve this on Oracle is not so simply. First, to get user sources you should query USER_SOURCE view where each function or stored procedure line is stored in one row.
To get a full body, you have to use a powerful hierarchical query clause SYS_CONNECT_BY_PATH.
Here is a query which returns you list of all stored procedures and functions in Oracle schema together with their bodies defined in one column:
SELECT routine_name, y.TYPE, TRANSLATE(LTRIM(x.text1, '/'), '/', ' ') routine_definition
FROM (SELECT name routine_name, LEVEL lvl, sys_connect_by_path(text, '/') text1
FROM USER_SOURCE
CONNECT BY LINE - 1 = PRIOR LINE AND name = PRIOR name) x,
(SELECT name, TYPE, MAX(line) AS maxline
FROM USER_SOURCE
GROUP BY name, TYPE) y
WHERE x.routine_name = y.name AND x.lvl = y.maxline
ORDER BY TYPE, routine_name
Compare database schema and data fast and easy with DBTYP.NET Studio
February 24th, 2010 by DLM
One of our customers reported us this error during reading stored procedures from Oracle database. What have caused this error? USER_SOURCE view retrieve name, type and line by line of function/stored procedure definition. Each line in a new row. We used hierarchical queries and sys_connect_by_path function to concatenate all of those lines in one which keeps the full function/stored procedure definition.
The problem is, that SQL in Oracle is able to return just 4000 bytes in one column. So, if any string is longer than 4000 bytes in concatenation you will get this error. And there is no possibility to fix it. You must change logic, use chunks or something similar (CLOBs).
As a small notice, T-SQL variables can be used for concatenation very big strings which exceeds 4000 characters. Again, you will not be able to return it back with SQL.
As a sample, the following code produces the error:
select rpad(' ',4000)||'1' as 4001StringLength
from TestTable
but the following is fine:
declare
testVar varchar2(32767);
begin
testVar := rpad(' ',4000)||'1'
end;
September 27th, 2009 by DLM
One of our customers reported us this error during reading stored procedures from Oracle database. What have caused this error? USER_SOURCE view retrieve name, type and line by line of function/stored procedure definition. Each line in a new row. We used hierarchical queries and sys_connect_by_path function to concatenate all of those lines in one which keeps the full function/stored procedure definition.
The problem is, that SQL in Oracle is able to return just 4000 bytes in one column. So, if any string is longer than 4000 bytes in concatenation you will get this error. And there is no possibility to fix it. You must change logic, use chunks or something similar (CLOBs).
As a small notice, T-SQL variables can be used for concatenation very big strings which exceeds 4000 characters. Again, you will not be able to return it back with SQL.
As a sample, the following code produces the error:
select rpad(' ',4000)||'1' as 4001StringLengthfrom TestTable
but the following is fine:
declaretestVar varchar2(32767);begintestVar := rpad(' ',4000)||'1'end;
April 27th, 2009 by DLM
New Features
MySQL has released a new version of database server – MySQL in version 5.4. Latest release was 5.1 with a really great features. This one brings not so much but also very important features:
- InnoDB storage engine can address more than 4 CPU’s/cores. Thjs helps that MySQL scale much better under huge application workloads.
- Till now, subqueries were well known as performance problematical. In version 5.4 subquery optimization has been improved a lot in a number of various use cases. As MySQL mentioned in the example below, execution time dropped from 12 minutes (9 000 000 reads) on MySQL 5.1 to just 1.8 seconds (153 000 reads) on MySQL 5.4
SELECT COUNT(l_orderkey) FROM lineitemWHERE l_linenumber=1 AND l_orderkey IN (SELECT o_orderkey FROM orders WHERE o_totalprice > 1000 AND o_custkey IN (SELECT c_custkey FROM customer WHERE c_address LIKE 'Le%'));
- MySQL 5.4 offers a new much better join algorithm which speed up execution time of multi-way joins like in the following example
SELECT COUNT(*) FROM part, lineitem WHERE l_partkey=p_partkey AND p_retailprice>2050 AND l_discount>0.04;
- Improved error handling – through the implementation of standard SQL (SQL 2003) SIGNAL and RESIGNAL operations in stored procedures, functions and triggers developers are able to signal rule violations during execution.
Must mention here that Oracle and SQL Server not implement this functionality but have their own mechanisms: Oracle through RAISE and SQL Server through RAISEERROR statements.
- INFORMATION_SCHEMA got a new objects PARAMETERS. We wait a lot on this.
- In prior version of MySQL out parameter were not possible in prepared statements. That has been changed now.
This is all from programmers perspective. Have a fun.
Compare SQL Server, Oracle and MySQL database with DBTYP.NET Studio
