|
This is in MS-SQL server BTW
|
|
|
|
|
Tuning a database is an art and there is no 1 silver bullet, you can only try a number of things and see if they are worth the changes.
For a start the size of your indexes should not concern you, on a heavily indexed table this is not unusual.
If you insert is a bacth job (with 100k records is should be) then it may be worth dropping the indexes for the insert and recreating them. This is a DRASTIC measure and should be tested for cost on another server.
Consider the changes to the clustered index suggested
Consider moving the indexes to another physical drive
Partitioning your data (think archiving but you can query across partitions)
These are but some of your options, I assume you have use query profiler to asses the cost of your queries.
|
|
|
|
|
"hi
i have a confusion i m using this query in asp.vb page now i have
Dim strsearch As String
strsearch = txtsearch1.Text
i want to search records form mytable with the value of strsearch.
strseach is input variable for search a record form mytable
please guide me about this
dadsubmit = New SqlDataAdapter("SELECT AuthorId, UrlUserName, Urlid, UrlAddress, ChooseMedia, Title, Description, ChooseThumbnail, ChooseTopic, Datetime, (SELECT COUNT(CommentId) AS Expr1 FROM CommentTable WHERE (CommentUrlid = Submitnewtable.Urlid)) AS cnt,
DATEDIFF(day, Datetime, GETDATE()) AS diff FROM Submitnewtable WHERE
Title like '%%' &strsearch , consumit)
there is error in block code
|
|
|
|
|
Hi, I don't know mush about VB.net but I think it will be
'%' & strsearch & '%'
Niladri Biswas
|
|
|
|
|
Try the following:
dadsubmit = New SqlDataAdapter(
String.Concat("SELECT AuthorId, UrlUserName, Urlid, UrlAddress, ChooseMedia, Title, ", _
"Description, ChooseThumbnail, ChooseTopic, Datetime, ", _
"(SELECT COUNT(CommentId) AS Expr1 FROM CommentTable WHERE ", _
"(CommentUrlid = Submitnewtable.Urlid)) AS cnt, ", _
"DATEDIFF(day, Datetime, GETDATE()) AS diff ", _
"FROM Submitnewtable WHERE Title like '%", strsearch.Trim(), "%';") _
, consumit)
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
|
Hi!
I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?
thank you
|
|
|
|
|
Looking for the 15th space won't help you, as there may be more than one space between words. What about punctuation? Do you need to take that into account?
Basically, you need to parse the string, finding one word at a time (and adding them into a new string) until you reach the end of your 15th word...
There are plenty of ways to do this. One simple way is to read the string one character at a time until you either reach the 15th word (at which point you have the answer you are after) or you reach the end of the string (at which point you can return some meaningful error such as 'string too short').
Have a crack at it and post again when you have some code to show us!!
|
|
|
|
|
If there is more than one space for e.g. 2 space I will count that in too so I will be printing only upto 14 words. could you please help. with below i can count number of words based on space. could you please give me one simple example as to how i can obtain without writing complex function.
SELECT LEN(text) - LEN(REPLACE(text, ' ', '')) + 1 from table
|
|
|
|
|
I think you are confusing words with characters...
If you are after 15 characters, simply use the left function.
select left(FIELDNAME, 15) from TABLENAME
|
|
|
|
|
Hi! I am after 15 words not characters. thanks
|
|
|
|
|
If you want to keep the beginning of a text, not necessarily 15 words, but as much as possible, not exceeding N characters, then you could search for the last space before position N, and keep everything up to that space.
This[^] would give some clues then.
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Try this
declare @str as varchar(100)
declare @startposition int
declare @endposition int
declare @stopingcondition int
declare @delimeter char(1)
set @startposition = 0
set @stopingcondition = 15
set @delimeter =''
set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data
--Program starts
;with num_cte as
(
select 1 as rn
union all
select rn +1 as rn
from num_cte
where rn <= len(@str)
)
, get_all_delimited_char_pos_cte as
(
select row_number()over(order by rn) cnt ,rn,chars
from num_cte
cross apply( select substring(@str,rn,1) AS chars) splittedchars
where chars = @delimeter
)
select @endposition = rn from get_all_delimited_char_pos_cte where cnt = @stopingcondition
select SUBSTRING(@str,@startposition,@endposition) as First15thWords
Output:
First15thWords
I would like to get first 15 word from a long sentence but not sure
The approach:
If you do a substring like
declare @str as varchar(100)
set @str = 'I would like to get first 15 word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data
select SUBSTRING(@str,0,68)
you will get the first 15 words.
So the challenge was to get the number 68(count by youself and you will find that)
By using the get_all_delimited_char_pos_cte CTE and with the help of cross apply I am able to get the position of the delimited characters and from there I just picked up the position of the 15th occurrence of the delimited charecter
Hope this helps
Niladri Biswas
modified on Wednesday, November 11, 2009 7:58 AM
|
|
|
|
|
Good afternoon. I am working on an automated booking system, and I am trying to figure a strategy for resource availability as it pertains to data storage.
The database has service providers in it (People who do things) and they are each available up to 24 hours a day 7 days a week. They might be available only on Monday from 1 to 2 AM. They need to have the ability to say they are available in different hour brackets.
I am scratching my head a little bit thinking about the database design. I could have 169 columns in the table (1 for userid, and then 1 for each hour of the week), but that sounds laborious. I also considered a binary style string 168 characters long where each hour of the week has a specific string position (1000000100000111110000), but that is equally or more laborious. I also considered the idea of allowing the user to store their information in a table that tracks the day of the week, and the start/end time of their availability. The table could have multiple rows per day so if the user is available from 1 to 5 and then 6 to 10, he or she could have two Monday rows in the DB, but I am not sold on this strategy either.
Anyways, does anyone have experience in this particular kind of data storage that they would like to share? Any and all thoughts are welcome.
Cheers
|
|
|
|
|
You may want to consider 7 columns with a 24 byte array in each column. A blank indicated availability. My reason for this is that is would be easy to generate queries like "who is available on Mondays ?".
select username from availTable where mondayAvail = ""
Your design really depends on how you think you will be using (querying) the data.
You may want to consider the ability to store a person's availability for a given time frame. Consider ... Joe is available Monday (9:00 - 17:00) for January, but after that he will switch to evenings (17:00 - 02:00). With that requirement, then I would go with an effective data in the front of the availability table which indicates FromThisDate ToThisDate on these days of week and these hours, the resource is available.
Sorry I couldn't be more definitive.
Good luck.
|
|
|
|
|
Quite insightful, thank you. In regards to the 24 bit array, when you say that a blank indicates availability how would you manage partial availability? Were you thinking the same sort of array as I suggested prior? (111100001111111111111111 indicates availability at all times aside from 5 to 9 AM)
|
|
|
|
|
Well I would say you can take your bits and .....
I would not even consider the bit type solution, purely from a downstream support aspect. Imagine coming in to support such a system.
I think the table solution is going to be more flexible and supportable and will be more queriable by your users (who probably have no idea what a bit is). I would have 2 tables, Dates and Times, allow the user to enter as many times for a day as they require, supply a propagate forward by week/month methods. This can then interact with a calendar control.
|
|
|
|
|
Hi,When I am Installing SQL Server 2008 advance series,at last I am getting one exception and stopping the installation.
The Error is : Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
I have tried in manyways,but not able to fix.Appreaciate your valuable advise on this Inadvance.
the following msg is the Summary Log file.you can go through it once.
Thanks,
Srikrishna
Overall summary:
Final result: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
Exit code (Decimal): -2068643839
Exit facility code: 1203
Exit error code: 1
Exit message: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
Start time: 2009-11-10 17:47:51
End time: 2009-11-10 18:29:49
Requested action: Install
Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.0.1600.22%26EvtType%3d0x429FDFA3%400x0A2FBD17%401211%401
Machine Properties:
Machine name: SRIKRISHNA-PC
Machine processor count: 2
OS version: Windows Vista
OS service pack:
OS region: India
OS language: English (United States)
OS architecture: x86
Process architecture: 32 Bit
OS clustered: No
Product features discovered:
Product Instance Instance ID Feature Language Edition Version Clustered
Package properties:
Description: SQL Server Database Services 2008
SQLProductFamilyCode: {628F8F38-600E-493D-9946-F4178F20A8A9}
ProductName: SQL2008
Type: RTM
Version: 10
SPLevel: 0
Installation location: c:\c9d261aac71184b75483\x86\setup\
Installation edition: EXPRESS_ADVANCED
User Input Settings:
ACTION: Install
ADDCURRENTUSERASSQLADMIN: False
AGTSVCACCOUNT: NT AUTHORITY\NETWORK SERVICE
AGTSVCPASSWORD: *****
AGTSVCSTARTUPTYPE: Disabled
ASBACKUPDIR: Backup
ASCOLLATION: Latin1_General_CI_AS
ASCONFIGDIR: Config
ASDATADIR: Data
ASDOMAINGROUP: <empty>
ASLOGDIR: Log
ASPROVIDERMSOLAP: 1
ASSVCACCOUNT: <empty>
ASSVCPASSWORD: *****
ASSVCSTARTUPTYPE: Automatic
ASSYSADMINACCOUNTS: <empty>
ASTEMPDIR: Temp
BROWSERSVCSTARTUPTYPE: Automatic
CONFIGURATIONFILE: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\ConfigurationFile.ini
ENABLERANU: True
ERRORREPORTING: False
FEATURES: SQLENGINE,REPLICATION,FULLTEXT,RS,BIDS,SSMS,SNAC_SDK,OCS
FILESTREAMLEVEL: 3
FILESTREAMSHARENAME: SQLEXPRESS2008
FTSVCACCOUNT: NT AUTHORITY\LOCAL SERVICE
FTSVCPASSWORD: *****
HELP: False
INDICATEPROGRESS: False
INSTALLSHAREDDIR: C:\Program Files\Microsoft SQL Server\
INSTALLSHAREDWOWDIR: C:\Program Files\Microsoft SQL Server\
INSTALLSQLDATADIR: <empty>
INSTANCEDIR: C:\Program Files\Microsoft SQL Server\
INSTANCEID: SQLExpress2008
INSTANCENAME: SQLEXPRESS2008
ISSVCACCOUNT: NT AUTHORITY\NetworkService
ISSVCPASSWORD: *****
ISSVCSTARTUPTYPE: Automatic
MEDIASOURCE: c:\c9d261aac71184b75483\
NPENABLED: 0
PID: *****
QUIET: False
QUIETSIMPLE: False
RSINSTALLMODE: DefaultNativeMode
RSSVCACCOUNT: NT AUTHORITY\LOCAL SERVICE
RSSVCPASSWORD: *****
RSSVCSTARTUPTYPE: Automatic
SAPWD: *****
SECURITYMODE: <empty>
SQLBACKUPDIR: <empty>
SQLCOLLATION: Latin1_General_CI_AI
SQLSVCACCOUNT: NT AUTHORITY\NETWORK SERVICE
SQLSVCPASSWORD: *****
SQLSVCSTARTUPTYPE: Automatic
SQLSYSADMINACCOUNTS: MINVESTAHYD\srikrishna.murthy
SQLTEMPDBDIR: <empty>
SQLTEMPDBLOGDIR: <empty>
SQLUSERDBDIR: <empty>
SQLUSERDBLOGDIR: <empty>
SQMREPORTING: False
TCPENABLED: 0
X86: False
Configuration file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\ConfigurationFile.ini
Detailed results:
Feature: Database Engine Services
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: SQL Client Connectivity SDK
Status: Passed
MSI status: Passed
Configuration status: Passed
Feature: SQL Server Replication
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Full-Text Search
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Reporting Services
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Management Tools - Basic
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Business Intelligence Development Studio
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Microsoft Sync Framework
Status: Passed
MSI status: Passed
Configuration status: Passed
Rules with failures:
Global rules:
Scenario specific rules:
Rules report file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\SystemConfigurationCheck_Report.htm
|
|
|
|
|
It is possible that you do not have the WMI service running. To activate the WMI service, go to 'add or remove programs' and then into turn on/off windows features.
Alternatvely there are issues if you have not cleanly uninstalled and earlier version. Mark Michaelis' Weblog[^] has an interesting article on this that may help sole your problem.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
I m working in c# .net application with sql server 2005...I have requirement of 1:N search from a record of over 1,00,000 of entries. How can I optimize search.Please suggest me .
|
|
|
|
|
The most efficient search mechanism in SQL is to use a fulltext index.
There's loads of stuff a google[^] search can give you on using a full text index.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
Make sure your table is properly indexed.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
IMO, 100000 rows is definitely not a 'heavy database'. If you properly index your tables, search should be very fast.
|
|
|
|
|
Hi Gents,
I have used two select queries in a stored procedure.. how can i retrieve the values from the two queries in a single datareader
for ex,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[SearchDoctor]
@fname varchar(50),
@lname varchar(50)
As
Select * from docregistration where fname=@fname and lname=@lname
Declare @Speciality varchar(50)
Select @Speciality=Speciality from docregistration where fname=@fname and lname=@lname
Select Speciality from Speciality where SpecialityID=@Speciality
GO
Thanks a lot in advance
Azeem,
Senior Dotnet Developer
|
|
|
|
|
You can't.
A DataReader itterates through a set of rows from a single result table.
What you need to use is a DataAdaptor with a dataset. Eg.
System.Data.DataSet myDS = new System.Data.DataSet();
System.Data.SqlClient.SqlDataAdapter myAdaptor
= new System.Data.SqlClient.SqlDataAdapter("Execute myProcedure;"
,new System.Data.SqlClient.SqlConnection(myConnectionString));
myAdaptor.Fill(myDS)
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|