Инструменты для сравнения структур и данных в БД
(версии бесплатны для частного некоммерческого использования)
dbForge Schema Compare for SQL Server
dbForge Data Compare for SQL Server
dbForge Studio for MySQL
OraDeveloper Studio
http://www.devart.com/ru/?gclid=CPvQlfbTjZsCFcwUzAodThs98g
вторник, 16 июня 2009 г.
понедельник, 15 июня 2009 г.
CLR функция для T-SQL которая возвращает таблицу
Код функции возвращающий таблицу как результат
Навеяно TSQL функцией которая строку вида '1,2,3,4,5' переворачивает в таблицу
НО! если строка большая, то начинаются дикие тормоза
Это решение подымает скорость!
=================================
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
private class TableResult
{
public SqlString Element;
public TableResult(SqlString element)
{
Element = element;
}
}
[SqlFunction(TableDefinition = "Element nvarchar(max)",
DataAccess = DataAccessKind.Read,
FillRowMethodName = "FillRow")]
public static IEnumerable Function1(string p)
{
ArrayList resultCollection = new ArrayList();
var ss = p.Split(new[] { ',' });
foreach (var s in ss)
resultCollection.Add(new TableResult(s));
return resultCollection;
//resultCollection.AddRange
//return (new List(p.Split(new[] { ',' }))
//.ConvertAll(s => new TableResult(s)));
}
public static void FillRow(
object Obj,
out SqlString element)
{
TableResult result = (TableResult)Obj;
element = result.Element;
}
};
===========================================
Поднять все это в MSSQL
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'SqlServerProject2')
DROP ASSEMBLY SqlServerProject2
go
CREATE ASSEMBLY SqlServerProject2 FROM 'C:\SqlServerProject2.dll'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS
GO
CREATE FUNCTION [dbo].[Function1](@p [nvarchar](max))
RETURNS TABLE (
[Element] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerProject2].[UserDefinedFunctions].[Function1]
go
select * from dbo.Function1('234567,222,233,123');
go
Навеяно TSQL функцией которая строку вида '1,2,3,4,5' переворачивает в таблицу
НО! если строка большая, то начинаются дикие тормоза
Это решение подымает скорость!
=================================
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
private class TableResult
{
public SqlString Element;
public TableResult(SqlString element)
{
Element = element;
}
}
[SqlFunction(TableDefinition = "Element nvarchar(max)",
DataAccess = DataAccessKind.Read,
FillRowMethodName = "FillRow")]
public static IEnumerable Function1(string p)
{
ArrayList resultCollection = new ArrayList();
var ss = p.Split(new[] { ',' });
foreach (var s in ss)
resultCollection.Add(new TableResult(s));
return resultCollection;
//resultCollection.AddRange
//return (new List
//.ConvertAll
}
public static void FillRow(
object Obj,
out SqlString element)
{
TableResult result = (TableResult)Obj;
element = result.Element;
}
};
===========================================
Поднять все это в MSSQL
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'SqlServerProject2')
DROP ASSEMBLY SqlServerProject2
go
CREATE ASSEMBLY SqlServerProject2 FROM 'C:\SqlServerProject2.dll'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS
GO
CREATE FUNCTION [dbo].[Function1](@p [nvarchar](max))
RETURNS TABLE (
[Element] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerProject2].[UserDefinedFunctions].[Function1]
go
select * from dbo.Function1('234567,222,233,123');
go
понедельник, 1 июня 2009 г.
Обработка ошибок для ASP.NET (elmah, Health Monitoring)
Обработка ошибок для ASP.NET
http://www.hanselman.com/blog/ELMAHErrorLoggingModulesAndHandlersForASPNETAndMVCToo.aspx
Исходники
http://code.google.com/p/elmah/
Альтернатива от MS - Health Monitoring
http://habrahabr.ru/blogs/net/27847/
How To: Use Health Monitoring in ASP.NET 2.0
http://www.hanselman.com/blog/ELMAHErrorLoggingModulesAndHandlersForASPNETAndMVCToo.aspx
Исходники
http://code.google.com/p/elmah/
Альтернатива от MS - Health Monitoring
http://habrahabr.ru/blogs/net/27847/
How To: Use Health Monitoring in ASP.NET 2.0
IVR-ссылки (AnswerDetectMachine)
Улучшатель для AnswerDetectMachine !!!
http://gotspeech.net/forums/1/6419/ShowThread.aspx
http://gotspeech.net/forums/1/6419/ShowThread.aspx
IVR-ссылки (счетчик в QA-activity)
Анализ сколько раз пользователь неправильно ввел на одном QA-activity
http://gotspeech.net/forums/thread/8606.aspx
http://gotspeech.net/forums/thread/8606.aspx
понедельник, 25 мая 2009 г.
Полезные запросы (размер таблиц)
select object_name(i.object_id) as objectName, i.name as indexName,
sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB
from sys.indexes i
join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
join sys.allocation_units a on p.partition_id = a.container_id
group by i.object_id, i.index_id, i.name
order by 1, 2
sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB
from sys.indexes i
join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
join sys.allocation_units a on p.partition_id = a.container_id
group by i.object_id, i.index_id, i.name
order by 1, 2
вторник, 11 ноября 2008 г.
SQL SERVER - Clear SQL Server Memory Caches
If SQL Server is running slow and operations are throwing errors due to lack of memory, it is necessary to look into memory issue. If SQL Server is restarted all the cache memory is automatically cleaned up. In production server it is not possible to restart the server. In this scenario following three commands can be very useful.
When executed following three commands will free up memory for SQL Server by cleaning up its cache.
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Reference : Pinal Dave (http://www.SQLAuthority.com)
=============== AND ===============================
For Microsoft SQL (MSSQL).
Use this to clear the cache and buffers to ensure comparison are accurate.
dbcc freeproccache
go
dbcc dropcleanbuffers
go
When executed following three commands will free up memory for SQL Server by cleaning up its cache.
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Reference : Pinal Dave (http://www.SQLAuthority.com)
=============== AND ===============================
For Microsoft SQL (MSSQL).
Use this to clear the cache and buffers to ensure comparison are accurate.
dbcc freeproccache
go
dbcc dropcleanbuffers
go
Подписаться на:
Сообщения (Atom)