星期四, 2月 20, 2014

MS SQL資料庫定序(Collation)的議題

問題
當兩個column join時,而兩個定序不同,這時這個sql session就直接死給你看

解決方法
  1. 從db設定(但資料要重建)
    db, column都可以指定
  2. sql query直接指定
    SELECT str FROM test ORDER BY str COLLATE CHINESE_TAIWAN_STROKE_CI_AS
CS_AI vs CI_AS 傻傻搞不清楚
  • CaseSensitive_AccentInsensitive
  • CaseInsensitive_AccentSensitive
其他還有WS (Width sensitivity),即全型、半型字是否相同

Reference

星期日, 2月 09, 2014

[書]even faster web sites performance-best practices for web developers

針對js, css, image, browser等深入探討,以求最佳的效能


Ch1. Understanding Ajax Performance

Trade-offs - Fast. Good. Cheap. Pick Two.
  • 低於Inefficiency line就會流失user
  • 先利用YSlow改善
  • 利用ajax可大副降n,當然前提要考量ajax本體application要小
  • 事後再抓資料呈現
  • DOM及CSS的處理比JS的速度慢的多,針對JS加速不如小心處理DOM及CSS

Ch.2 Createing Responsive Web Applications

What is fast enough
  • ajax實現快速反應系統
  • 只針對不夠快的程式調整,重點是如何定義「不夠快」
    • 0.1 second : Limit for users feeling that they are directly manipulating objects in the UI. 例如hover
    • 1 second: Limit for users feeling that they are freely navigating the command space without having to unduly wait for the computer. 0.2秒~1秒會感覺在運作
    • 10 seconds : Limit for users keeping their attention on the task.
  • 也就是說application要在0.1s內啟動, 在1s內有所回應
  • 超過1s會讓user感覺到慢, 超過10s.... 該調程式了...

Meausuring Latency
  • 可利用firebug的profiler找出js的執行時間
  • Threading - js沒有mutilthread,不要想有的沒的 這只會把程式搞複雜
  • HTML5有Web Workers可達到threads效果,拿來當背景程式跑長時間的script
  • 如果browser不支援,google有Gears plugin-in

Effects of Memory Use on Response Time

  • memory也會造成效能issue
  • 在run GC時,會run整個heap,回收不再使用的物件,因此大量使用下,會讓系統發生短暫凍結
  • OS會提供virtual memory這比實體memory慢的多
  • Troubleshooting Memory Issues
    • 針js的memory沒有神器,只能追問題所在,參考 http://blog.pavlov.net/2008/03/11/firefox-3-memory-usage/
    • Use the delete keyword to remove JavaScript objects that are no longer needed
      from memory.

ch.3 Splitting the Initial Payload

  • 必要的application script先讀,不重要的後讀
  • 不相關的script模組就不要讀
  • 後讀的ui相關script要能先呈現loading圖示,避免user使用(lazy-loaded code)
  • stub function
    • 先回應空的,等載完後再覆寫
  • splitting script & CSS都能達到效果

Ch4. Loading Scripts without Blocking

  • 下載external script 會造成blocking(下載中的資源不會被擋)



ie8, Safari 4, Chrome 2可同時下載,但得等js載完且跑完,才會再下載其他資源
有方法可避開blocking的問題,但同時也有race conditions問題
即script如有順序問題,後者先執行會發生錯誤
解決方法
  • XHR Eval
    • 當資源載後,用eval
    • 缺點:需在同一domain
  • XHR Injection
    • 把js當dom方式插入
    • 缺點:比eval慢
  • Script in Iframe
    • 開iframe把script當html下載
    • 缺點:需在同一domain, iframe耗資源
  • Script DOM Element
    • 方法:create個dom,改src,簡單易懂
    • var scriptElem = document.createElement('script');
      scriptElem.src = 'http://anydomain.com/A.js';
      document.getElementsByTagName('head')[0].appendChild(scriptElem);
  • Script Defer
    • 增加defer屬性,可平行下載
    • 缺點:只有ie支援
  • document.write Script Tag
    • 類似defer,可平行下載
    • 缺點:只有ie會平行下載
Ensuring (or Avoiding) Ordered Execution
利用script src寫法可確認依序下載


Ch.5 Coupling Asynchronous Scripts

Ch.6 Positioning Inline Scripts

Ch.7 Writing Efficient JavaScript
Managing Scope

Ch.8 Scaling with Comet

Ch.9 Going Beyond Gzipping

  • Besides proper configuration of HTTP caching headers, enabling gzip compression is typically the most important technique for speeding up your web page
  • 目前的browser都支援gzip,但會因firewall或防毒軟體限制而禁作gzip
  • 針對這些可以有以下做法
    • Design to Minimize Uncompressed Size
      • Use Event Delegation (選完才載入)
    • Use relative URLs
      • 同網站內的uri可以用相對路徑,這樣文字相對少
    • Strip whitespace
    • Strip attirbute quotes
      • html裡的attribute內容如為純文字(含括號,底線,分號等)可省略雙引號
    • Avoid inline styling
      • style多為重覆使用,寫在css共用
    • Alias JavaScript
      • Wasteful
        • var foo = $("foo);
        • foo.style.left = "0";
        • foo.style.right = "0";
      • Better
        • var foo = $("foo).style;
        • foo.left = "0";
        • foo.right = "0";
  • 以上約可節省11.7的傳輸量
Educate Users
  • 發生user不支援Accept-Encoding,應給個訊息提供,教育user
    • ex. Your Internet connection is slowed because it does not allow compression.
    • 不過經過proxy來的是無法解決的
Fix this Hide

Ch.10 Optimizing Images

  • GIF - 適用小動畫
  • PNG - 適用圖表(graphics - icons, logos, diagrams) 
  • JPG - 適用照片(photos)
Truecolor versus palette image formats
Interlacing

Stripping JPEG Metadata

  • Comments
  •  Application-specific (e.g., Photoshop) internal information
  •  EXIF information such as camera make and model, the date the photo was taken, the geolocation of the photo, thumbnails, or even audio

Ch.11 Sharding Dominant Domains

Ch.12 Flushing the Document Early

Ch.13 Using Iframes Sparingly

Ch.14 Simplifying CSS Selectors

  • 將css置於head中,以提高progressive rendering. (See High Performance Web Sites, Chapter 5.)
  • ie可能發生css expression被執行上千次
  • 避免inline styling,以減少download size
CSS Selectors有以下幾種方式
  • ID SelectorsExample: #toc { margin-left: 20px; }Simple and efficient
  • Class SelectorsExample: .chapter { font-weight: bold; }
  • Type SelectorsExample: A { text-decoration: none; }a lightweight way to add styling to all elements of a specified type, without having to add any extra characters
  • Adjacent Sibling SelectorsExample: H1 + #toc { margin-top: 40px; }
  • Child SelectorsExample: #toc > LI { font-weight: bold; }找出#toc下所有的LI(1層)
  • Descendant SelectorsExample: #toc A { color: #444; }找出#toc下所有的A(n層)
  • Universal SelectorsExample: * { font-family: Arial; }
  • Attribute SelectorsExample: [href="#index"] { font-style: italic; }
  • Pseudo-Classes and Pseudo-ElementsExample: A:hover { text-decoration: underline

以下討論performace

Rightmost First

Consider the following rule:
#toc > LI { font-weight: bold; }
由於id最有效,因此大家會認為先找#toc,再找底下的LI是較好的方法(左到右)
但其實browser是從右到左,因此這段selector反而相當沒效率,但先找出所有的LI,再看他的parent是不是#toc
可以想見Descendant Selectors就更差了,所有的node除非找到#toc,否則都要走完

Writing Efficient CSS Selectors

  • Avoid universal rules
    In addition to the traditional definition of universal selectors, Hyatt lumps adjacent
    sibling selectors, child selectors, descendant selectors, and attribute selectors into
    this category of “universal rules.” He recommends using ID, class, and tag selectors
    exclusively.
  • Don’t qualify ID selectors
    Because there is only one element in the page with a given ID, there’s no need to
    add additional qualifiers. For example, DIV #toc is unnecessary and should be

    simplified to #toc.
  • Don’t qualify class selectorsInstead of qualifying class selectors for specific tags, extend the class name to be specific to the use case. For example, change LI .chapter to .li-chapter , or better yet, .list-chapter.
  • Make rules as specific as possibleDon’t be tempted to build long selectors, such as OL LI A. It’s better to create a class, such as .list-anchor, and add it to the appropriate elements.
  • Avoid descendant selectorsDescendant selectors are typically the most expensive to process. Child selectors are often what’s intended and can be more efficient. It’s even better to follow the next guideline to avoid child selectors as well.
  • Avoid tag-child selectorsIf you have a child selector that is based on a tag, such as #toc > LI > A, use a class associated with each of those tag elements, such as .toc-anchor
  • Question all usages of the child selectorThis is another reminder to review all places where child selectors are used, and replace them with specific classes when possible.
  • Rely on inheritanceLearn which properties are inherited, and avoid rules that specify these inherited styles. For example, specify list-style-image on the list element instead of on eachlist item element. Consult the list