SQL Server Performance Forum – Threads Archive

How to optimize the query(Asked by Microsoft)

Hello All,
The following question was asked by Microsoft during interview session There is a one table say FileDetails with only one column Details varchar(2000)
The column contains file details in the following format \serverlocation codePathNameDate ReceivedTime ReceivedFileName Now to find out the following query with select statement ,lot of string function need to be use to get the results. Supoose there are million of million rows in this table. So if we follow the above approach it will become very slow. So how to optimize the query to get results in a mimimum time…. qry:
1>List all the files name for a particular Date?
2>List all the files name for a particular location?
3>List all the files name for a particular Server?
4>List all the files name for a particular Path?
Need response asap. Thanks & Regards
Niladri

---

Make a normalized structure with separate columns for file name, date received, location etc.

---

As said, normalise the table
http://www.datamodel.org/NormalizationRules.html Otherwise you need to split the data and compare it accordingly Madhivanan Failing to plan is Planning to fail

---

s_niladri, It seems you are posting only interview questions. What was your answer there?
Madhivanan Failing to plan is Planning to fail

---

Hi Madhivanan,
I also gave the same answer that is splitting the single column into multiple columns so that query will be faster. Then they asked if table contains only one column how can we optimize the query. I have told them the query which included a lot of string function which make the query slower.I couldn’t tell them how to optimize the query if they follow single column in a table.
Niladri

---

Without changing table structure or creating indexed view splitting varchar into multiple columns you can’t do much to optimize the query. You can add an index to improve performance of query 3 and that’s all.

---

LOcked this one as well, as per other post. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>