Archive

Posts Tagged ‘Database’

Comma separated values in mysql


MY SQL:

GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.

Syntax GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR ‘sep’])
Quick Example SELECT GROUP_CONCAT(city) FROM cities GROUP BY state;
Separator Comma (,) by default, ” eliminates separator
NULL Values Skipped
Max Length 1024 by default, specified by group_concat_max_len system variable

Sample:

CREATE TABLE cities
   (
     city VARCHAR(70),
     state CHAR(2)
   );
 
   INSERT INTO cities VALUES ('San Francisco', 'CA');
   INSERT INTO cities VALUES ('San Diego', 'CA');
   INSERT INTO cities VALUES ('Los Angeles', 'CA');
 
   INSERT INTO cities VALUES ('Austin', 'TX');
   INSERT INTO cities VALUES ('Houston', 'TX');

   SELECT state, GROUP_CONCAT(city)
   FROM cities
   GROUP BY state;
The result:
CA San Francisco,San Diego,Los Angeles
TX Austin,Houston
SQL Server:
SELECT state,
STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'')
FROM cities c
GROUP BY state

Hope this help !
Rahul Bhatia

What is the difference between IEnumerable and IQueryable

March 12, 2013 1 comment

In LINQ to query data from database and collections, we use IEnumerable and IQueryable for data manipulation. IEnumerable is inherited by Iqueryable, hence it has all the features of it and except this, it has its own features. Both have its own importance to query data and data manipulation. Let’s see both the features and take the advantage of both the features to boost your LINQ Query performance.

IEnumerable  IEnumerable  IQueryable
Namespace System.Collections Namespace System.Linq Namespace
Derives from No base interface Derives from IEnumerable
Deferred Execution Supported Supported
Lazy Loading Not Supported

Supported

How does it work While querying data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data. Hence does more work and becomes slow. While querying data from database, IQueryable execute select query on server side with all filters. Hence does less work and becomes fast.
Suitable for LINQ to Object and LINQ to XML queries. LINQ to SQL queries.
Custom Query Doesn’t supports. Supports using CreateQuery and Execute methods.
Extension mehtod
parameter
Extension methods supported in IEnumerable takes functional objects. Extension methods supported in IEnumerable takes expression objects i.e. expression tree.
When to use when querying data from in-memory collections like List, Array etc. when querying data from out-memory (like remote database, service) collections.
Best Uses In-memory traversal Paging

IEnumerable Example

MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith(“S”));
list = list.Take<Employee>(10);

Generated SQL statements of above query will be:

SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

IQueryable Example

MyDataContext dc = new MyDataContext ();
IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith(“S”));
list = list.Take<Employee>(10);

Generated SQL statements of above query will be:

SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

Hope it helps!

Tips to improve Entity Framework Performance

March 12, 2013 14 comments

LINQ to Entity is a great ORM for querying and managing database. It offers a lot of things, so it is mandatory to know about performance of it. These are right up to a certain point as LINQ comes with its own penalties. There are some tips and tricks that we should keep in mind while designing and query database using entity framework ORM. Here is a list of some tips that I would like to share with you.

  1. Avoid to put all the DB Objects into One Single Entity Model

    Entity Model specifies a single unit of work, not all our database. If we have many database objects that are not connected to one another or these(log tables, objects used by batch processes etc.) are not used at all. Hence these objects are consuming space in the memory and cause performance degrades. So try to make separate entity models of related database objects.

  2. Disable change tracking for entity if not needed

    Whenever you retrieve the data only for reading purpose, not for modification then there is no need of object tracking. So disable object tracking by using MergeOption as below:

    NorthwindDataContext context = new NorthwindDataContext();

    context.tblCities.MergeOption = MergeOption.NoTracking;

    This option allows us to turn off the object cache and unnecessary identity management of the objects.

  3. Use Pre-Generating Views to reduce response time for first request

    When the object of ObjectContext is created first time in the application, the entity framework creates a set of classes that is required to access the database. This set of classes is called view and if your data model is large then creating the view may delay the web application response to the first request for a page. We can reduce this response time by creating view at compile time by using T4 template or EdmGen.exe command-line tool.

  4. Avoid fetching all the fields if not required

    Avoid fetching not required fields from the database. Suppose I have table of Customer with 20 fields and I am interested only in three fields – CustomerID, Name, Address then fetch only these three fields instead of fetching all the fields of the Customer table.

    //Bad Practice

    var customer =

    (from cust in dataContext.Customers

    select cust).ToList();

    //Good Practice

    var customerLite =

    (from cust in dataContext.Customers

    select new {

    customer. CustomerID,

    customer.Name,

    customer.Address

    }). ToList ();

  5. Choose appropriate Collection for data manipulation

    In linq we have Var, IEnumerable, IQueryable, IList type collection for data manipulation. Each collection has its importance and performance impact on the query, so beware of using all these collection for data manipulation.

  6. Use Compiled Query wherever needed

    Make a query to compiled query if it is frequently used to fetch records from the database. This query is slow in first time but after that it boost the performance significantly. We use Compile method of CompiledQuery class for making compiled query.

    Suppose you required to retrieve customers details again and again based on city then make this query to compiled query like as

    // create the entity object

    NorthwindEntities mobjentity = new NorthwindEntities();

    //Simple Query

    IQueryable lstCus = from customer in mobjentity.tblCustomers

    where customer.City == “Delhi”

    select customer;

    //Compiled Query

    Func> compiledQuery

    = CompiledQuery.Compile>(

    (ctx, city) =>from customer in ctx.Customers

    where customer.City == city

    select customer);

    In above query we are passing the string parameter city for filtering the records.

  7. Retrieve only required number of records

    When we are binding data to grid or doing paging, retrieve only required no of records to improve performance. This can achieved by using Take,While and Skip methods.

    // create the entity object

    NorthwindEntities mobjentity = new NorthwindEntities();

    int pageSize=10,startingPageIndex=2;

    List lstCus = mobjentity.tblCustomers.Take(pageSize)

    .Skip(startingPageIndex * pageSize)

    .ToList();

  8. Avoid using Contains

    In LINQ, we use contains method for checking existence. It is converted to “WHERE IN” in SQL which cause performance degrades.

  9. Avoid using Views

    Views degrade the LINQ query performance costly. These are slow in performance and impact the performance greatly. So avoid using views in LINQ to Entities.

  10. Debug and Optimize LINQ Query

    If you want to debug and optimize your query then LINQ Pad is a great tool for this purpose. I am a big fan of LINQ Pad. It is very useful for query construction, debugging and optimization.

    IQueryable lstCus = from customer in mobjentity.tblCustomers

    where customer.City == “Delhi”

    select customer;

    lstCus.Dump();

    Dump method of LINQ Pad give the result of above query in the result window.


Visual Studio – Database Project Not Loading


Have you ever created a Database project and after some time you are not able to load it in the Visual Studio? Probably you might be getting following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

The database project requires an active database connection to load at design time. Probably it uses that to compile/verify scripts. The problem here is Visual Studio is not able to connect to any such instance. Following are few solutions (these worked for me)

  1. Make sure the instance to which you want to connect is running and allows remote connections.
  2. After performing step 1, if it is still not loading then you may need to start the SQL Browser Service. Make sure it’s running.
  3. If the problem is still there, it has to be fixed from Visual Studio itself. Actually database project do not store to which instance it can connect. It’s a Visual Studio setting.In VS2008 go to Tool > Options > Database Tools > Design-time Validation Database > Connection Options. Specify your instance name here like “\SQLEXPRESS”

Also If you upgraded your database project from 2005 to 2008 and if  you try to open the 2008 database project solution file then you may get the following error:

Coluld not load file or assembly ‘System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken = 89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

To resolve this you need to install SQL Server CE 3.5 SP1 from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&displaylang=en

Hope it helps !

Cannot drop database because it is currently in use.


This is a very common error when DROP Database is command is executed and the database is not dropped.

The following commands will raise above error:
USE AdventureWorks;
GO
DROP DATABASE AdventureWorks;
GO

Solution:
The following commands will not raise an error and successfully drop the database:

USE Master;
GO
DROP DATABASE AdventureWorks;
GO

If you are still getting the error after you try using
use master
go
drop database (databaseName)
go

Close SQL Server Management Studio completely. Open it again and connect as normal. Now you will be able to drop the database with
use master
go
drop database (databaseName)
go

Hope it helps!

COALESCE with DISTINCT in SQLServer

November 6, 2012 Leave a comment

If you want comma separated values from columns it is so simple. Use COALESCE function provided by SQL Server.

DECLARE @csv varchar(max)
select @csv = COALESCE(@csv + ‘,’ , ‘ ‘) + cast(id  AS VARCHAR) from tablename
print @csv

COALESCE with DISTINCT Values

If you want, DISTINCT values in COALESCE function use inner table like below.

DECLARE @csv varchar(max)
select @csv = COALESCE(@csv + ‘, ‘, ‘ ‘) + cast(id  AS VARCHAR) from
(SELECT DISTINCT id FROM TableName)
print @csv

Hope it helps!