IT Job Search Engine

I've been working on integrating RSS feeds into a SQL Server db . Using OPENXML , you can get some powerful inputting , without to much data manipulation.  This http://www.itjobfeed.com/ grabs the feeds from a list , via DTS , scans for syntax errors  and then enters link and meta information into the db .

It would be useful to see more xml lists under certain sectors to have the same data structures.

They are inviting more companies,organisations,agencies to supply them with feeds to expand the search engine

ITJobfeed  was sold to a large job site company - and have now integrated the engine into their services.

Related Posts

OpenXML - more than 8000 characters

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Top 10 SQL Server 2005 DW Performance Issues

Good article on Technet , regarding real world issues for Datawarehousing performance.http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx

Related Posts

SQL Server Statistics and Datawarehouse

Datawarehouse Trends - In - memory

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Setting Remote Auditing For SQL Server

Recently ,I used osql for some remote auditing. osql is a standard app that comes with SQL Server 2000 (T-SQL statements, system procedures, and script files can be executed). I audited some login activity for a suspected security breach. Essentially I was analysing attempted and failed login attempts . To configure this auditing, open Enterprise Manager, select a database server, right-click Properties, go to the Security tab, and set the required audit level. I recommend that you don't audit to the Application log; otherwise, SQL Server writes audit information about user-login activity to two places simultaneously and unnecessarily degrade system performance. After changing audit settings, restart the database.

Use the osql command line to enable auditing. Use this tool to enable auditing on remote SQL Server systems. If you used an account with DBA privileges on the target database server to log on to Windows, type:

osql -S -E 

-S tells osql.exe to connect to a remote server and myservername is the name of the database server or instance. The -E option tells osql.exe to use the credentials with which you logged on to Windows to establish a trusted database connection. If you didn't use an account with DBA privileges, you need to type:

osql -S -U -P

-U tells osql.exe that the following argument is the username (DBA privileges required) and -P signals that the next argument is the password for the specified account.

After enabling auditing for the default database or for an instance, the database server logs all activity to the data directory specified during the installation process. A disadvantage of using this method for audit log information is that it captures everything. To achieve a granular approach to this types of auditing, you'll need to turn to Server-Side Traces.

SQL Server writes all auditable activity to a file with the format audittrace_YYYYMMDDHHMMSS.trc, where YYYYMMDDHHMMSS is the log's creation time by year, month, day, hour, minute, and second

You can use SQL Server Profiler to view the log files that SQL Server creates.

Related Posts

Powershell : SQLCMD and invoke-expression

SQL Traces and c2 auditing are different

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL 2005 : OPTIMIZE FOR

Recently, I had the following problem to consider. Two similar SELECT statements , the one difference being the WHERE clause. If you notice the statement 1 uses a variable whereas statement 2 uses date literals.

The problem related to the difference in performance. Statement 2 consistently performed better , by a margin of 10 seconds on average. How could this be?I ran the same queries on a 2 different machines , cleared caches but still the difference remained consistent.

(statement 1)
Declare @Date as datetime
set @Date = '01-04-06'
SELECT prod as code
FROM orders
WHERE (ship_date between @Date and dateadd(d,6,@Date))
GROUP BY prod

(statement 2)
SELECT prod as code
FROM orders
WHERE (ship_date between '01-04-06' and dateadd(d,6,'01-04-06'))
GROUP BY prod

The first thing I did to diagnose was check the execution plan. I noticed that INDEX SCAN was being used rather that INDEX SEEK. This pointed to low selectivity in the data , meaning that the data posessed a relatively low level of uniqueness. Essentially the index in place had a low level of helpfulness. Usually a SEEK is more effective than a SCAN , but there are exceptions. For example , if the table is small and memory cached.In this case the table had over 1 million records

(As an aside if all else fails when it comes to hitting indexes , you can state the actual index to hit directly withing the SQL statement , such as :

SELECT prod as code
FROM orders with (index (ix_my_index index))
WHERE (ship_date between '01-04-06' and dateadd(d,6,'01-04-06'))
GROUP BY prod

The second thing I did (a SQL 2005 feature) was to utilise the OPTIMIZE FOR .The statement then became:

SELECT prod as code
FROM orders
WHERE (ship_date between '01-04-06' and dateadd(d,6,'01-04-06'))
GROUP BY prod
OPTION (OPTIMIZE FOR (@Date = '20060405'))

The literal used in the hint just needs to be one that suggests to the optimizer what should be the desired query plan. Essentially one that generates a similar rowcount estimate to values you are likely to use for the @Date value. Also, a colleague pointed out to me that using the date format 'dd-mm-yy' has different interpretation whereas 'YYYYMMDD' only has 1 interpretation

Eventually, I didn't get the queries equal but only on avearge a couple of seconds difference.

SQL Server : Order of Operators

Something which may seem basic, but in many SQL statements I analyse for performance optimisation, the same mistakes are recurring. When a WHERE clause is used the actual operator used will influence the query speed. That is not to say , every statement has the option of using the optimal operator.
It's worth keeping in mind the order of the operator , the order starts with the fastest performer.
=
<,<=,>,>=
LIKE
<>